龙空技术网

oracle 开启 LogMiner归档日志配置

编程少年 80

前言:

眼前看官们对“oracle开启日志”大致比较关怀,小伙伴们都需要知道一些“oracle开启日志”的相关资讯。那么小编同时在网络上收集了一些对于“oracle开启日志””的相关内容,希望姐妹们能喜欢,小伙伴们一起来了解一下吧!

检查归档日志是否打开

[oracle@dsjjs ~]$ sqlplus sys/jundu123 as sysdba;SQL> archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 6Current log sequence 8
配置归档日志大小,并指定归档日志保存路径(大小和路径根据实际情况调整)
SQL> alter system set db_recovery_file_dest_size = 200G;SQL> alter system setdb_recovery_file_dest = '/opt/oracle/oradata/recovery_area' scope=spfile;
关闭数据库
SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.
挂载实例
SQL> startup mount;ORACLE instance started.Total System Global Area 4275781632 bytesFixed Size 2260088 bytesVariable Size 922747784 bytesDatabase Buffers 3338665984 bytesRedo Buffers 12107776 bytesDatabase mounted
启动归档模式
SQL> alter database archivelog;Database altered.
启动数据库
SQL> alter database open; Database altered.
检查归档日志是否成功
SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 6Next log sequence to archive 8Current log sequence 8
创建归档日志专用用户表命名(路径根据实际情况调整)
SQL> create tablespace logminer_user_tslogging datafile '/opt/oracle/tablespace/LOGMINER_USER_TS.dbf'size 100m autoextend onnext 50m maxsize 20480m extent management local;Tablespace created.
创建归档日志专用用户
SQL> create user logminer_user identified by logminer_userdefault tablespace logminer_user_tstemporary tablespace temp profile default;User created.
给用户授权
SQL> grant connect,resource to logminer_user;Grant succeeded.

非容器模式,还需要执行以下授权

GRANT CREATE SESSION TO logminer_user ;GRANT SELECT ON V_$DATABASE to logminer_user ;GRANT FLASHBACK ANY TABLE TO logminer_user ;GRANT SELECT ANY TABLE TO logminer_user ;GRANT SELECT_CATALOG_ROLE TO logminer_user ;GRANT EXECUTE_CATALOG_ROLE TO logminer_user ;GRANT SELECT ANY TRANSACTION TO logminer_user ;GRANT CREATE TABLE TO logminer_user ;GRANT LOCK ANY TABLE TO logminer_user ;GRANT CREATE SEQUENCE TO logminer_user ;GRANT EXECUTE ON DBMS_LOGMNR TO logminer_user ;GRANT EXECUTE ON DBMS_LOGMNR_D TO logminer_user ;GRANT SELECT ON V_$LOG TO logminer_user ;GRANT SELECT ON V_$LOG_HISTORY TO logminer_user ;GRANT SELECT ON V_$LOGMNR_LOGS TO logminer_user ;GRANT SELECT ON V_$LOGMNR_CONTENTS TO logminer_user ;GRANT SELECT ON V_$LOGMNR_PARAMETERS TO logminer_user ;GRANT SELECT ON V_$LOGFILE TO logminer_user ;GRANT SELECT ON V_$ARCHIVED_LOG TO logminer_user ;GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO logminer_user ;
开启补充日志

补充日志的类型有:最小补充日志、标识关键字段补充日志两大类。

SQL> alter database add supplemental log data;Database altered.SQL> alter database add supplemental log data (all) columns;Database altered.

标签: #oracle开启日志 #oracle开启归档界面 #oracle中怎么建立日志表 #oraclefacelogging #oracle启用归档