23 February, 2020

Quickly creating a Standby Database in 19c

A quick overview

1.  Create the parameter file initSTDBYDB.ora with additional parameters
  change or add DB_UNIQUE_NAME to be STDBYDB
  change the location of control files
  add fal_server to be the lookup name for the Primary (e.g. ORCLCDB)
  add log_archive_dest_2 to specify the Primary Service and DB_UNIQUE_NAME (note : If you are using "log_archive_dest", you can't use "log_archive_dest_2" to co-exist.  A default DB_RECOVERY_FILE_DEST location is preferable)
  add db_file_name_convert and log_file_name_convert to map file names to new directories (if they are to be different or, for example, if creating the Standby on the same server !!)  --- ensure that you have the new directories (or ASM DiskGroups) available on the Standby with the right permissions (including directories for PDBs and the PDBSEED) !
  change any other hardcoded directory names (e.g. for adump)

2.  Create a listener.ora and/or a new listener with a static SID_NAME entry for the Standby DB

3.  Add an entry for the Standby  in the Primary tnsnames.ora and for the Primary in the Standby tnsnames.ora

4.  Add at least one Standby Redo Log file to the Primary Database

5.  Ensure that you have the password for the SYS account (or will you be using SYSDG ?) on the Primary and copy the Password file to the Stadnby

6.  Start the Standby listener

7.  STARTUP NOMOUNT the Standby Instance (remember to have the ORACLE_SID set !!)

8.  Start rman on the Primary with :
rman target sys/manager auxiliary sys/manager@STDBYDB
and then issue the command
duplicate target database for standby from active database dorecover;


and thus the execution will be as :

oracle19c>rman target sys/manager auxiliary sys/manager@STDBYDB

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Feb 23 23:38:59 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2778483057)
connected to auxiliary database: ORCLCDB (not mounted)

RMAN> duplicate target database for standby from active database dorecover;

Starting Duplicate Db at 23-FEB-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
current log archived

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/opt/oracle/product/19c/dbhome_1/dbs/orapwSTDBYDB'   ;
}
executing Memory Script

Starting backup at 23-FEB-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 device type=DISK
Finished backup at 23-FEB-20

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/opt/oracle/oradata/STDBYDB/control01.ctl';
   restore clone primary controlfile to  '/opt/oracle/oradata/STDBYDB/control02.ctl' from
 '/opt/oracle/oradata/STDBYDB/control01.ctl';
}
executing Memory Script

Starting backup at 23-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/oracle/product/19c/dbhome_1/dbs/snapcf_ORCLCDB.f tag=TAG20200223T233924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 23-FEB-20

Starting restore at 23-FEB-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-FEB-20

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/opt/oracle/oradata/STDBYDB/temp01.dbf";
   set newname for tempfile  2 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf";
   set newname for tempfile  3 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/opt/oracle/oradata/STDBYDB/system01.dbf";
   set newname for datafile  3 to
 "/opt/oracle/oradata/STDBYDB/sysaux01.dbf";
   set newname for datafile  4 to
 "/opt/oracle/oradata/STDBYDB/undotbs01.dbf";
   set newname for datafile  5 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf";
   set newname for datafile  6 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to
 "/opt/oracle/oradata/STDBYDB/users01.dbf";
   set newname for datafile  8 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf";
   set newname for datafile  10 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf";
   set newname for datafile  11 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf";
   set newname for datafile  12 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/opt/oracle/oradata/STDBYDB/system01.dbf"   datafile
 3 auxiliary format
 "/opt/oracle/oradata/STDBYDB/sysaux01.dbf"   datafile
 4 auxiliary format
 "/opt/oracle/oradata/STDBYDB/undotbs01.dbf"   datafile
 5 auxiliary format
 "/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf"   datafile
 6 auxiliary format
 "/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf"   datafile
 7 auxiliary format
 "/opt/oracle/oradata/STDBYDB/users01.dbf"   datafile
 8 auxiliary format
 "/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf"   datafile
 9 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf"   datafile
 10 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf"   datafile
 11 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf"   datafile
 12 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /opt/oracle/oradata/STDBYDB/temp01.dbf in control file
renamed tempfile 2 to /opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf in control file
renamed tempfile 3 to /opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
output file name=/opt/oracle/oradata/STDBYDB/system01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
output file name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
output file name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
output file name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
output file name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
output file name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
output file name=/opt/oracle/oradata/STDBYDB/users01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-FEB-20

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/opt/oracle/archivelog/ORCLCDB/1_41_1007421686.dbf" auxiliary format
 "/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf"   archivelog like
 "/opt/oracle/archivelog/ORCLCDB/1_42_1007421686.dbf" auxiliary format
 "/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf"   ;
   catalog clone archivelog  "/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf";
   catalog clone archivelog  "/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 23-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=41 RECID=9 STAMP=1033170130
output file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=42 RECID=10 STAMP=1033170130
output file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 23-FEB-20

cataloged archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=1 STAMP=1033170133

cataloged archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=2 STAMP=1033170133

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf

contents of Memory Script:
{
   set until scn  4658614;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-FEB-20
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 41 is already on disk as file /opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf
archived log for thread 1 with sequence 42 is already on disk as file /opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf
archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf thread=1 sequence=41
archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf thread=1 sequence=42
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-FEB-20

contents of Memory Script:
{
   delete clone force archivelog all;
}
executing Memory Script

released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 device type=DISK
deleted archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=1 STAMP=1033170133
deleted archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=2 STAMP=1033170133
Deleted 2 objects

Finished Duplicate Db at 23-FEB-20

RMAN>


Note :  For simplicity, I didn't use the SPFILE specification in the DUPLICATE command to create and update an SPFILE at the Standby.  I am using a simple initSTDBYDB.ora pfile


In the next blog post, I will be covering how to begin (and then monitor) shipping of redo from the Primary to the Standby.


09 February, 2020

Basic Replication -- 13 : Some Interesting SYS tables

I found an interesting SQL in the AWR report from my previous blog post.

What do you think this SQL statement does ?

DELETE FROM SYS.MVREF$_STMT_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ# = :B1


Here are some interesting objects :
{note : They don't exist in the 11.2.0.4 database that I have ; they do exist in 12.2.0.1, but this test is in 19.3}

SQL> l
  1  select object_name, object_type
  2  from dba_objects
  3  where owner = 'SYS'
  4  and object_name like 'MVREF$%'
  5* order by 2,1
SQL> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
MVREF$_STATS_SEQ               SEQUENCE
MVREF$_CHANGE_STATS            TABLE
MVREF$_RUN_STATS               TABLE
MVREF$_STATS                   TABLE
MVREF$_STATS_PARAMS            TABLE
MVREF$_STATS_SYS_DEFAULTS      TABLE
MVREF$_STMT_STATS              TABLE

7 rows selected.

SQL>


Right now, the SYS.MVREF$_STMT_STATS table appears to be empty.
SQL> desc SYS.MVREF$_STMT_STATS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MV_OBJ#                                   NOT NULL NUMBER
 REFRESH_ID                                NOT NULL NUMBER
 STEP                                      NOT NULL NUMBER
 SQLID                                     NOT NULL VARCHAR2(14)
 STMT                                      NOT NULL CLOB
 EXECUTION_TIME                            NOT NULL NUMBER
 EXECUTION_PLAN                                     SYS.XMLTYPE STORAGE BINARY

SQL>


It would be interesting to know how Oracle is using this and the other MVREF$% tables.
SYS.MVREF$_CHANGE_STATS obviously captures DML operations

This SYS.MVREF$_RUN_STATS captures the last refresh operation (*does it only capture the last operation ?*) And what does SYS.MVREF$_STATS capture :

SQL> l
  1  select *
  2  from SYS.MVREF$_RUN_STATS
  3* where MVIEWS='"HEMANT"."MV_1"'
SQL> /

RUN_OWNER_USER# REFRESH_ID NUM_MVS_TOTAL NUM_MVS_CURRENT MVIEWS             BASE_TABLES  METHOD ROLLBACK P R PURGE_OPTION
--------------- ---------- ------------- --------------- ------------------ ------------ ------ -------- - - ------------
PARALLELISM  HEAP_SIZE A N O NUMBER_OF_FAILURES START_TIME                 END_TIME                   ELAPSED_TIME LOG_SETUP_TIME
----------- ---------- - - - ------------------ -------------------------- -------------------------- ------------ --------------
LOG_PURGE_TIME C    TXNFLAG ON_COMMIT_FLAG
-------------- - ---------- --------------
            106        245             1               1 "HEMANT"."MV_1"                                 Y N            1
          0          0 Y N N                  0 09-FEB-20 09.55.33.000000  09-FEB-20 09.55.49.000000            16              1
                                                PM                         PM
             9 Y          0              0


SQL>
SQL> l
  1  select mviews, count(*) from sys.mvref$_run_Stats group by mviews
  2* order by 1
SQL> /

MVIEWS                                       COUNT(*)
------------------------------------------ ----------
"HEMANT"."MV_1"                                     1
"HEMANT"."MV_2"                                     8
"HEMANT"."MV_DEPT", "HEMANT"."MV_EMP"               1
"HEMANT"."MV_FAST_NOT_POSSIBLE"                     1
"HEMANT"."MV_OF_SOURCE"                             1
"HEMANT"."NEW_MV"                                   2
"HEMANT"."NEW_MV_2_1"                               1
"HEMANT"."NEW_MV_2_2"                               2
"HR"."HR_MV_ON_COMMIT"                              1
"HR"."MY_LARGE_REPLICA"                             1

10 rows selected.

SQL>
SQL> desc sys.mvref$_run_stats
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 RUN_OWNER_USER#                                                          NOT NULL NUMBER
 REFRESH_ID                                                               NOT NULL NUMBER
 NUM_MVS_TOTAL                                                            NOT NULL NUMBER
 NUM_MVS_CURRENT                                                          NOT NULL NUMBER
 MVIEWS                                                                            VARCHAR2(4000)
 BASE_TABLES                                                                       VARCHAR2(4000)
 METHOD                                                                            VARCHAR2(4000)
 ROLLBACK_SEG                                                                      VARCHAR2(4000)
 PUSH_DEFERRED_RPC                                                                 CHAR(1)
 REFRESH_AFTER_ERRORS                                                              CHAR(1)
 PURGE_OPTION                                                                      NUMBER
 PARALLELISM                                                                       NUMBER
 HEAP_SIZE                                                                         NUMBER
 ATOMIC_REFRESH                                                                    CHAR(1)
 NESTED                                                                            CHAR(1)
 OUT_OF_PLACE                                                                      CHAR(1)
 NUMBER_OF_FAILURES                                                                NUMBER
 START_TIME                                                                        TIMESTAMP(6)
 END_TIME                                                                          TIMESTAMP(6)
 ELAPSED_TIME                                                                      NUMBER
 LOG_SETUP_TIME                                                                    NUMBER
 LOG_PURGE_TIME                                                                    NUMBER
 COMPLETE_STATS_AVAILABLE                                                          CHAR(1)
 TXNFLAG                                                                           NUMBER
 ON_COMMIT_FLAG                                                                    NUMBER

SQL> desc sys.mvref$_stats
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 MV_OBJ#                                                                  NOT NULL NUMBER
 REFRESH_ID                                                               NOT NULL NUMBER
 ATOMIC_REFRESH                                                           NOT NULL CHAR(1)
 REFRESH_METHOD                                                                    VARCHAR2(30)
 REFRESH_OPTIMIZATIONS                                                             VARCHAR2(4000)
 ADDITIONAL_EXECUTIONS                                                             VARCHAR2(4000)
 START_TIME                                                                        TIMESTAMP(6)
 END_TIME                                                                          TIMESTAMP(6)
 ELAPSED_TIME                                                                      NUMBER
 LOG_SETUP_TIME                                                                    NUMBER
 LOG_PURGE_TIME                                                                    NUMBER
 INITIAL_NUM_ROWS                                                                  NUMBER
 FINAL_NUM_ROWS                                                                    NUMBER
 NUM_STEPS                                                                         NUMBER
 REFMET                                                                            NUMBER
 REFFLG                                                                            NUMBER

SQL>
SQL> select mv_obj#, count(*)
  2  from sys.mvref$_stats
  3  group by mv_obj#
  4  /

   MV_OBJ#   COUNT(*)
---------- ----------
     73223          1
     73170          1
     73065          1
     73244          1
     73079          8
     73094          1
     73197          2
     73113          2
     73188          1
     73167          1
     73110          1

11 rows selected.

SQL>
SQL> desc sys.mvref$_stats_params
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 MV_OWNER                                                                 NOT NULL VARCHAR2(128)
 MV_NAME                                                                  NOT NULL VARCHAR2(128)
 COLLECTION_LEVEL                                                         NOT NULL NUMBER
 RETENTION_PERIOD                                                         NOT NULL NUMBER

SQL> select count(*)
  2  from sys.mvref$_stats_params;

  COUNT(*)
----------
         0

SQL> desc sys.mvref$_stats_sys_defaults
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 COLLECTION_LEVEL                                                         NOT NULL NUMBER
 RETENTION_PERIOD                                                         NOT NULL NUMBER

SQL> select * from sys.mvref$_stats_sys_defaults
  2  /

COLLECTION_LEVEL RETENTION_PERIOD
---------------- ----------------
               1               31

SQL>



Oracle has been introducing some more "internal" tables to trace MView Refresh operations.