How to change default tablespace for LogMiner tables in Oracle Streams

1) Create a new tablespace for Log miner

CREATE TABLESPACE LOGMNR_TBS DATAFILE ‘/oradata01/STREAMDB/logmnr_tbs01.dbf' size 100m;

2) Move the objects from SYSAUX (default tablespace) to new tablespace.

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/

3) If there are active log miner session, then we'll get following error

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMNR_TBS');
END;
ORA-01356: active logminer sessions found
ORA-06512: at “SYS.DBMS_LOGMNR_D”, line 233
ORA-06512: at line 2

This error suggests that, there are active logminer sessions in the database.

There are two types of log mining sessions

1) Persistent (CAPTURE process)
2) Non-persistent

We need to stop the LogMiner sessions to change the tablespace.

– Stop Non-persistent

BEGIN
DBMS_LOGMNR.END_LOGMNR();
END;
/
PL/SQL procedure successfully completed.

NOTE: If no logminer sessions are active, we'll get ORA-01307: no LogMiner session is currently active

– Stop Persistent

BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE'); END;
/
PL/SQL procedure successfully completed.

4) Then change the LogMiner tablespace

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/
PL/SQL procedure successfully completed.

5) Confirm the objects are moved to new tablespace

SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = 'LOGMNR_TBS';


6) Start capture process

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE');
END;
/
PL/SQL procedure successfully completed.

Issue with running catupgrd.sql while Database upgrade from 10.2.0.4 to 11.2.0.3:

Issue with running catupgrd.sql while Database upgrade from 10.2.0.4 to 11.2.0.3:

Getting below error while running catupgrd.sql scripts while DB upgrade.

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                *
ERROR at line 1:
ORA-01722: invalid number

Cause:
------------
The Pre-Upgrade Script is not creating the registry$database table & inserting the Platform DST Patch Information.The error may also appear if the the registry$database table exists,  but does not contain the required information.

Solution:
-----------------

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit

SQL> create table registry$database_b as select * from registry$database;

Table created.

select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit

SQL> INSERT into registry$database
  2                      (platform_id, platform_name, edition, tz_version)
  3                 VALUES ((select platform_id from v$database),
  4                         (select platform_name from v$database),
  5                          NULL,
  6                         (select version from v$timezone_file));

1 row created.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit
         13 Linux x86 64-bit                                             4

SQL> commit;

Commit complete.

SQL> delete from sys.registry$database where TZ_VERSION is NULL;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit                                             4

Ref Doc: catupgrd.sql fails With ORA-01722 Invalid Number after running the Pre-Upgrade Script [ID 1466464.1]