How to work around ORA-03206 when running the RCU (Repository Creation Utility) used by ODI

During installation you may encounter the error

ORA-03206: maximum file size of (13107200) blocks in AUTOEXTEND clause is out of range

In my case, the RCU attempted to create a temporary tablespace with autoextend of 100G. This is too much for a smallfile tablespace, and the error is thrown.
I checked the database creation scripts, and it is indeed created with smallfile as the default tablespace type.

To solve the problem, alter your database default setting:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

and retry the operation.

Documentation for Oracle 12.2 can be found here

How to create a proxy authenticated user in Oracle

Create a general user for authentication:

CREATE USER APP_POOL
IDENTIFIED BY app_pool_users123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO APP_POOL;

To illustrate how proxy authentication can be used efficiently, I create two roles:

create role app_pool_role1 not identified;
create role app_pool_role2 not identified;

Grant object privileges on two different tables to the two new roles:

grant select on SCOTT.DEPT to app_pool_role1;
grant select on SCOTT.EMP to app_pool_role2;

Create a user:

CREATE USER VEGARD
IDENTIFIED BY vegard123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:

GRANT CREATE SESSION TO VEGARD;
GRANT APP_POOL_ROLE1 TO VEGARD;
GRANT APP_POOL_ROLE2 TO VEGARD;
ALTER USER VEGARD DEFAULT ROLE ALL;

Change the user so it connects through a proxy user.
Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:

ALTER USER VEGARD
GRANT CONNECT THROUGH APP_POOL
WITH ROLE APP_POOL_ROLE1;

Let's connect to the database:

sqlplus app_pool[vegard]/app_pool_users123@pdb01

Verify the connection and session settings by using sys_context:

select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
       decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
       sys_context('USERENV','CURRENT_SCHEMA') "current schema",
       sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
       decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
       sys_context('USERENV','PROXY_USER') "proxy user"
from dual;

Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:

AuthIdentity used	auth method	current schema	grantee of privileges used	identification type	proxy user
VEGARD	proxy	VEGARD	VEGARD	Password	APP_POOL

Which roles are enabled?

select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
from dual
union
select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
from dual
;

Result shows that only APP_POOL_ROLE1 is enabled:

Role granted?
APP_POOL_ROLE1: TRUE
APP_POOL_ROLE2: FALSE

Let's verify that the roles are actually working.
Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:

VEGARD@pdb01 SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


VEGARD@pdb01 SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

There is nothing preventing the user VEGARD from connecting directly to the database:

sqlplus vegard/vegard123@pdb01
VEGARD@pdb01 SQL> show user
USER is "VEGARD"

When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:

VEGARD@vegdb01 SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

Some advantages with using proxy connections:

* No more password administration for individual users
* Proxy authentication allows for role based access control
* Identity preservation of the real user behind the proxy user is automatically set up

Keep in mind that you still have to create and provision the end user accounts.

How to change location for the redo log files in physical standby database

Short background:
After a cold restore of the database files from standbyserver1 to standbyserver2, the redo logfiles where incorrectly registered in the database's control file.
This resulted in an error about the missing file every time the database opened or mounted:

Errors in file /u01/oracle/diag/rdbms/prodbb01_stby2/proddb01/trace/proddb01_m000_38117.trc:
ORA-00312: online log 3 in thread 1: /fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Since this is a standby database it is not stopping the database from functioning as intended, but it was annoying nevertheless.
Here is how I fixed the problem:

Shutdown the database, cancel managed recovery and open it in mounted mode:

sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate
startup mount

Allow file renaming on the stadby by temporarily setting STANDBY_FILE_MANAGEMENT to MANUAL.

alter system set STANDBY_FILE_MANAGEMENT=manual scope=memory;

If this is not done, Oracle will throw error

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

Rename the file(s):

alter database rename file '/fra/PRODDB01_STBY1/onlinelog/o1_mf_3__dm491kvf_.log' to '/fra/PRODDB01_STBY2/onlinelog/o1_mf_3__df26ddnv_.log';

Depending on your configuration, start managed reply in mounted state, or open the database in real-time query mode. I am doing the latter:

alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

How to create a compressed index using advanced index compression

Applicable for Oracle 12.1 and onwards.

Basic syntax:

CREATE INDEX MYIDX1 
ON MYTABLE(COL1, COL2)
COMPRESS ADVANCED LOW;

The above statement will create a compressed index in the connected user's default tablespace, using Oracle Advanced Index Compression.

In my environment, I tried this on a B-tree index on a 62 million row table. The size dropped from 3136 to 2368 MB, a 25 percent reduction.

An index can also be altered to use advanced index compression, like this:

ALTER INDEX MYIDX1 REBUILD COMPRESS ADVANCED LOW;

If it's important to allow DML on the index during rebuild, add ONLINE, like this:

ALTER INDEX MYIDX1 REBUILD COMPRESS ADVANCED LOW ONLINE;