Bald DBA

I had a user delete the data in a table that they didn’t mean to. It took them a week to notify us, so no chance to use flashback to get the data back. I was not looking forward to restoring 100TB of datafiles and recovery logs just to restart a couple GBs of data. Thankfully the database is running 19c, and rman can now do a table recovery.

I tried to recover the table

run {
  set auxiliary instance parameter file to '/opt/tempam/pfile.ora';
  recover table amyers.mytable until time 'trunc(sysdate-12)'
  auxiliary destination '/opt/tempam'
  remap table 'amyers'.'mytable':'mytable_recovered';
}

I had 20TB of space provisioned to /opt/tempam. To do table recovery, the system, sysaux, undo and the source table tablespaces are restored. in my case this should come to about 3TB.

After waiting an hour for everything to restore and roll forward, I get the following error in the final step where it should be exporting the table with datapump

RMAN-06960: EXPDP> ORA-31626: job does not exist
ORA-31637: cannot create job TSPITR_EXP_enpf_BDAD for user SYS
ORA-39062: error creating master process DM00
ORA-31613: Master process DM00 failed during startup.
RMAN-05122: There is insufficient disk space 951901 MB to perform table recovery of 1921807 MB.
RMAN-05122: There is insufficient disk space 951901 MB to perform table recovery of 1058623 MB.

This is a lie, there was plenty of space.

Turns out this is a known bug, Bug 9109785 : ORA-1555 TRANSPORT TABLESPACE FAILS WITH ORA-01555 DURING THE EXPDP PHASE. 

The work around is to prevent the database from being dropped after the failure by adding the “keep auxiliary” statement to the restore, and manually performing the datapump export.

run{  
  set auxiliary instance parameter file to '/opt/tempam/pfile.ora';
  recover table amyers.mytable until time 'trunc(sysdate-12)'
  auxiliary destination '/opt/tempam' keep auxiliary
  remap table 'amyers'.'mytable':'mytable_recovered';
}

This is a little useful sql I used this morning for stripping out all the additional information that dbms_metadata.get_ddl outputs. This strips the create index statements down to their bare bones.

set linesize 32767
SET TRIMSPOOL ON
SET TRIMOUT ON
SET WRAP OFF
set pagesize 0
 
begin
       dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', TRUE);
       dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
end;
/
 
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
  FROM all_indexes
WHERE     table_owner = UPPER ('OWNER')
       AND table_name IN ('TABLE1', 'TABLE2');

I started seeing this today in one of our databases

RMAN> resync catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 01/24/2020 11:45:49
RMAN-10015: error compiling PL/SQL program
RMAN-10014: PL/SQL error 0 on line 2620 column 18: Statement ignored
RMAN-10014: PL/SQL error 306 on line 2620 column 18: wrong number or types of arguments in call to 'CHECKDATAFILEFORSTANDBY'

I found metalink article 2360045.1 which states that it is a catalog version mismatch, however running upgrade catalog doesn’t resolve the problem. 

I remember hitting this error a long time ago, and although I can’f find the reason why, running upgrade catalog two times in a row seems to fix the problem (so typing upgrade catalog 4 times). 

So I have been having trouble getting the oracle wallet to auto open, and it’s always messed up in gv$encryption_wallet

select * from gv$encryption_wallet;

   INST_ID WRL_TYPE		WRL_PARAMETER STATUS   WALLET_TYPE   WALLET_OR  FULLY_BAC    CON_ID
---------- ------------ ---------------------- ------------- ---------- ------------ ------
         4 FILE 		OPEN                   AUTOLOGIN     SINGLE     NO           0
         1 FILE			OPEN                   AUTOLOGIN     SINGLE     NO           0
         3 FILE			NOT_AVAILABLE          UNKNOWN       SINGLE     UNDEFINED    0
         2 FILE			NOT_AVAILABLE          UNKNOWN       SINGLE     UNDEFINED    0

According to Oracle this is is because TNS_ADMIN is not set, so I set it and several other suggested variables.

$ srvctl setenv database -d oradb1 -T "ORACLE_UNQNAME=oradb1"
$ srvctl setenv database -d oradb1 -T "ORACLE_BASE=/u01/app/oracle"
$ srvctl setenv database -d oradb1 -T "ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/"
$ srvctl setenv database -d oradb1 -T "TNS_ADMIN=/u01/app/oracle/product/12.2.0.1/db/network/admin/"

$ srvctl getenv database -d oradb1
oradb1:
ORACLE_UNQNAME=oradb1
ORACLE_BASE=/u01/app/oracle
TNS_ADMIN=/u01/app/oracle/product/12.2.0.1/db/network/admin/
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/

After restarting the database, the ACFS volume now shows 

select * from gv$encryption_wallet;

   INST_ID WRL_TYPE             WRL_PARAMETER                    STATUS   WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- -------------------- -------------------------------- -------- -------------------- --------- --------- ----------
         1 FILE                 /u01/app/oracle/wallet/oradb1/   OPEN     AUTOLOGIN            SINGLE    NO                 0
         2 FILE                 /u01/app/oracle/wallet/oradb1/   OPEN     AUTOLOGIN            SINGLE    NO                 0
         3 FILE                 /u01/app/oracle/wallet/oradb1/   OPEN     AUTOLOGIN            SINGLE    NO                 0
         4 FILE                 /u01/app/oracle/wallet/oradb1/   OPEN     AUTOLOGIN            SINGLE    NO                 0

I noticed a long running query in one of my databases this morning.

  SELECT TABLESPACE,
           ROUND ( ( (max_size_mb - used_space + free_space_mb) / max_size_mb),
                  :"SYS_B_00")
         * :"SYS_B_01"
             AS pct_free
    FROM (  SELECT ts.tablespace_name                             TABLESPACE,
                     ROUND (SUM (NVL (fs.bytes, :"SYS_B_02")))
                   / :"SYS_B_03"
                   / :"SYS_B_04"
                       free_space_mb,
                   ROUND (SUM (df.bytes)) / :"SYS_B_05" / :"SYS_B_06" used_space,
                     ROUND (
                         SUM (
                             CASE autoextensible
                                 WHEN :"SYS_B_07" THEN df.maxbytes
                                 ELSE df.bytes
                             END))
                   / :"SYS_B_08"
                   / :"SYS_B_09"
                       AS max_size_mb,
                   ts.block_size
                       AS block_size
              FROM dba_data_files df
                   LEFT OUTER JOIN (  SELECT file_id, SUM (bytes) bytes
                                        FROM dba_free_space fs
                                    GROUP BY file_id) fs
                       ON df.file_id = fs.file_id
                   INNER JOIN dba_tablespaces ts
                       ON df.tablespace_name = ts.tablespace_name
             WHERE ts.contents = :"SYS_B_10"
          GROUP BY ts.tablespace_name, ts.block_size)
ORDER BY pct_free DESC

Its computing tablespace size. The average runtime is 272.03 sec, or 4.5 minutes. This isn’t right, its running in a few seconds in other databases.

I started looking at the query in OEM, and noticed almost all the execution time was being spent on a FTS of X$KTFBUE. 

A little googling turned up the following article:

Fixed Table x$ktfbue has not statistics

My issue appears to be the same, there are not statistics on teh X$KTFBUE table.

 SYS pp1oim1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OOWNER                        : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
IM_IMCU_COUNT                 :
IM_BLOCK_COUNT                :
IM_STAT_UPDATE_TIME           :
SCAN_RATE                     :
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
GLOBAL_STATS                  :
USER_STATS                    :
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
??????????????????

So gather stats on the table

 
 SYS pp1oim1> exec DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KTFBUE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.24
 SYS pp1oim1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OOWNER                        : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      : 158077
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   : 66
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
IM_IMCU_COUNT                 :
IM_BLOCK_COUNT                :
IM_STAT_UPDATE_TIME           :
SCAN_RATE                     :
SAMPLE_SIZE                   : 158077
LAST_ANALYZED                 : 09-sep-2019 10:32:52
GLOBAL_STATS                  : YES
USER_STATS                    : NO
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
??????????????????

With the new statistics the query finishes in a few seconds.