Oracle as a Source Database

sqlplus / as sysdba

-- Optional: Create a tablespace of an MDB user (The tablespace name and path need to be modified based on the actual requirement)
CREATE TABLESPACE mdb_tbs DATAFILE '/u01/app/oracle/oradata/ORCL/mdb_tbs.dbf' SIZE 1024M AUTOEXTEND OFF;

-- Mandatory: Create an MDB user (The username and password need to be modified based on the actual requirement)
CREATE USER mdb_user IDENTIFIED BY "Enmo@123" DEFAULT TABLESPACE mdb_tbs QUOTA UNLIMITED ON mdb_tbs;

GRANT CREATE SESSION TO mdb_user;
GRANT FLASHBACK ANY TABLE TO mdb_user;
GRANT SELECT ANY TABLE TO mdb_user;
GRANT SELECT_CATALOG_ROLE TO mdb_user;
GRANT EXECUTE_CATALOG_ROLE TO mdb_user;
GRANT SELECT ANY TRANSACTION TO mdb_user;

GRANT CREATE TABLE TO mdb_user;
GRANT LOCK ANY TABLE TO mdb_user;
GRANT CREATE SEQUENCE TO mdb_user;

GRANT EXECUTE ON DBMS_LOGMNR TO mdb_user;
GRANT EXECUTE ON DBMS_LOGMNR_D TO mdb_user;

GRANT SELECT ON V_$DATABASE to mdb_user;
GRANT SELECT ON V_$LOG TO mdb_user;
GRANT SELECT ON V_$LOG_HISTORY TO mdb_user;
GRANT SELECT ON V_$LOGMNR_LOGS TO mdb_user;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO mdb_user;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO mdb_user;
GRANT SELECT ON V_$LOGFILE TO mdb_user;
GRANT SELECT ON V_$ARCHIVED_LOG TO mdb_user;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO mdb_user;
GRANT SELECT ON V_$TRANSACTION TO mdb_user;

sqlplus / as sysdba

-- Optional: Create a tablespace of an MDB user (The tablespace name and path need to be modified based on the actual requirement)
-- Connect to the CBD container by default
CREATE TABLESPACE mdb_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/mdb_tbs.dbf' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Switch the connection to the PDB container, pdb_name:ORCLPDB1
alter session set container=ORCLPDB1; 
CREATE TABLESPACE mdb_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/mdb_tbs.dbf' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Mandatory: Create an MDB user (The username and password need to be modified based on the actual requirement)
conn / as sysdba
CREATE USER c##mdbuser IDENTIFIED BY "Enmo@123" DEFAULT TABLESPACE mdb_tbs QUOTA UNLIMITED ON mdb_tbs CONTAINER=ALL;

-- Note!! Please execute
alter user c##mdbuser set container_data=all container=current;

-- Note!! Please execute
alter user c##mdbuser set container_data=all container=current;

GRANT CREATE SESSION TO c##mdbuser CONTAINER=ALL;
GRANT SET CONTAINER TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$DATABASE to c##mdbuser CONTAINER=ALL;
GRANT FLASHBACK ANY TABLE TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ANY TABLE TO c##mdbuser CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO c##mdbuser CONTAINER=ALL;
GRANT EXECUTE_CATALOG_ROLE TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO c##mdbuser CONTAINER=ALL;
GRANT LOGMINING TO c##mdbuser CONTAINER=ALL;

GRANT CREATE TABLE TO c##mdbuser CONTAINER=ALL;
GRANT LOCK ANY TABLE TO c##mdbuser CONTAINER=ALL;
GRANT CREATE SEQUENCE TO c##mdbuser CONTAINER=ALL;

GRANT EXECUTE ON DBMS_LOGMNR TO c##mdbuser CONTAINER=ALL;
GRANT EXECUTE ON DBMS_LOGMNR_D TO c##mdbuser CONTAINER=ALL;

GRANT SELECT ON V_$LOG TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$LOG_HISTORY TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_LOGS TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$LOGFILE TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##mdbuser CONTAINER=ALL;
GRANT SELECT ON V_$TRANSACTION TO c##mdbuser CONTAINER=ALL;

COL DEST_NAME FOR A33
COL DESTINATION FOR A55
COL ERROR FOR A55
SET LINES 188 PAGES 111
SELECT DEST_NAME, DESTINATION, ERROR
  FROM V$ARCHIVE_DEST
 WHERE STATUS = 'VALID';

DEST_NAME           DESTINATION                ERROR
------------------- -------------------------- ------------------
LOG_ARCHIVE_DEST_1  USE_DB_RECOVERY_FILE_DEST

sqlplus / as sysdba

-- Use FRA (automatic management)
alter system set db_recovery_file_dest_size=100G;
alter system set db_recovery_file_dest='/opt/oracle/oradata/recovery_area' scope=spfile;

-- Use a manually specified archive path
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1='enable' scope=spfile;

-- Restart the database to the mount mode.
shutdown immediate
startup mount

-- Enable the archive mode.
alter database archivelog;

-- Open the database.
alter database open;

-- Check the archive mode.
archive log list

-- The output is as follows: 
-- Database log mode              Archive Mode  -- The archive mode has been enabled. 
-- Automatic archival             Enabled
-- Archive destination            USE_DB_RECOVERY_FILE_DEST
-- Oldest online log sequence     49609
-- Next log sequence to archive   49613
-- Current log sequence           49613

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

ALTER TABLE inventory.customers ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;