Oracle为源库

sqlplus / as sysdba

-- 可选步骤： 创建 MDB 用户表空间（表空间名称和路径需依据实际情况修改）
CREATE TABLESPACE mdb_tbs DATAFILE '/u01/app/oracle/oradata/ORCL/mdb_tbs.dbf' SIZE 1024M AUTOEXTEND OFF;

-- 必要步骤： 创建 MDB 用户（用户名和密码可按实际情况修改）
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

-- 可选步骤： 创建 MDB 用户表空间（表空间名称和路径需依据实际情况修改）
-- 默认连接CBD容器
CREATE TABLESPACE mdb_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/mdb_tbs.dbf' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

-- 切换连接PDB容器 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;

-- 必要步骤： 创建 MDB 用户（用户名和密码可按实际情况修改）
conn / as sysdba
CREATE USER c##mdbuser IDENTIFIED BY "Enmo@123" DEFAULT TABLESPACE mdb_tbs QUOTA UNLIMITED ON mdb_tbs CONTAINER=ALL;

-- 注!!! 请执行
alter user c##mdbuser set container_data=all container=current;

-- 注!!! 请执行 授权CDB用户可查询PDB
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

-- 使用 FRA (自动管理)
alter system set db_recovery_file_dest_size=100G;
alter system set db_recovery_file_dest='/opt/oracle/oradata/recovery_area' scope=spfile;

-- 使用手动指定的归档路径
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;

-- 重启数据库到 mount 模式
shutdown immediate
startup mount

-- 启用归档模式
alter database archivelog;

-- 打开数据库
alter database open;

-- 检查归档模式
archive log list

-- 示例输出如下：
-- Database log mode              Archive Mode  -- 归档模式已启用
-- 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;