Oracle常用命令（DMHS同步时使用）

1.查看Oracle字符集

SELECT USERENV('LANGUAGE') FROM DUAL;
SELECT * FROM NLS_DATABASE_PARAMETERS;

2.Oracle开启归档
(1)查看是否开启归档

SQL> ARCHIVE LOG LIST

(2)启动到MOUNT状态

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

(3)创建归档目录

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/oracle/archive';
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM ARCHIVE LOG ALL; --RAC所有节点归档

3.DMHS需要Oracle开启附加日志
(1)最小补充日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

(2)主键补充日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA(PRIMARY KEY) COLUMNS;

(3)唯一索引补充日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA(UNIQUE) COLUMNS;

(4)外键补充日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(FOREIGN KEY ) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA(FOREIGN KEY ) COLUMNS;

(5)全字段补充日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(ALL) COLUMNS ;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA(ALL) COLUMNS ;

(6)查询附加日志是否开启

SELECT DATABASE_ROLE,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SELECT SUPPLEMENTAL_LOG_DATA_MIN "MIN",SUPPLEMENTAL_LOG_DATA_PK "PK",SUPPLEMENTAL_LOG_DATA_UI "UI",SUPPLEMENTAL_LOG_DATA_FK "FK",SUPPLEMENTAL_LOG_DATA_ALL "ALL" FROM V$DATABASE;

4.Oracle创建同步用户
(1)DMHS同步用户创建及授权（SYS用户执行）

ALTER SESSION SET CONTAINER=HZQPDB;--容器数据库需要切换到容器内创建

使用DMHS同步时需要的权限

--可单独为DMHS用户创建表空间使用，如不创建，则默认使用USERS表空间
--CREATE TABLESPACE DMHS DATAFILE '+DATA/oracle/oradatafile/DMHS01.dbf' SIZE 1G AUTOEXTEND OFF;
CREATE TABLESPACE DMHS DATAFILE '/oradata/pdb01/DMHS01.dbf' SIZE 1G AUTOEXTEND OFF;
--CREATE USER DMHS IDENTIFIED BY DMHS QUOTA 1024M ON USERS;
CREATE USER DMHS IDENTIFIED BY DMHS DEFAULT TABLESPACE DMHS QUOTA UNLIMITED ON DMHS;
--SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='DMHS';
--ALTER USER DMHS DEFAULT TABLESPACE DMHS;
--GRANT UNLIMITED TABLESPACE TO DMHS;
--ALTER USER DMHS QUOTA 100M ON DMHS;
GRANT CONNECT,RESOURCE TO DMHS;
GRANT SELECT ON SYS.V_$INSTANCE TO DMHS;
GRANT SELECT ON SYS.V_$DATABASE TO DMHS;
GRANT SELECT ON SYS.V_$SESSION TO DMHS;
GRANT SELECT ON SYS.V_$PARAMETER TO DMHS;
GRANT SELECT ON SYS.GV_$PARAMETER TO DMHS;
GRANT SELECT ON SYS.GV_$INSTANCE TO DMHS;
GRANT SELECT ON SYS.GV_$ARCHIVE_DEST TO DMHS;
GRANT SELECT ON SYS.GV_$ARCHIVE TO DMHS;
GRANT SELECT ON SYS.GV_$LOG TO DMHS;
GRANT SELECT ON SYS.GV_$LOGFILE TO DMHS;
GRANT SELECT ON SYS.DBA_TABLES TO DMHS;
GRANT SELECT ON SYS.OBJ$ TO DMHS;
GRANT SELECT ON SYS.USER$ TO DMHS;
GRANT SELECT ON SYS.COL$ TO DMHS;
GRANT SELECT ON SYS.DBA_CONS_COLUMNS TO DMHS;
GRANT SELECT ON SYS.DBA_CONSTRAINTS TO DMHS;
GRANT SELECT ON SYS.LOB$ TO DMHS;
GRANT SELECT ON SYS.TABPART$ TO DMHS;
GRANT SELECT ON SYS.TAB$ TO DMHS;
GRANT SELECT ON SYS.TABSUBPART$ TO DMHS;
GRANT SELECT ON SYS.TABCOMPART$ TO DMHS;
GRANT EXECUTE ON DBMS_FLASHBACK TO DMHS;
GRANT LOCK ANY TABLE TO DMHS;
GRANT SELECT ANY TABLE TO DMHS;
GRANT SELECT ANY DICTIONARY TO DMHS;

PS1：使用DTS工具，DMHS用户迁移结构化数据需要的权限

GRANT EXECUTE ANY PROCEDURE TO DMHS;
GRANT SELECT ANY SEQUENCE TO DMHS; 
GRANT SELECT ANY TABLE TO DMHS; 
GRANT EXECUTE ANY TYPE TO DMHS; 
GRANT DEBUG ANY PROCEDURE TO DMHS; 

PS2：使用DTS工具，使用DMHS迁移数据时，需要的权限

GRANT SELECT ON SYS.V_$INSTANCE TO DMHS;
GRANT SELECT ON SYS.V_$DATABASE TO DMHS;
GRANT SELECT ON SYS.V_$SESSION TO DMHS;
GRANT SELECT ON SYS.V_$PARAMETER TO DMHS;
GRANT SELECT ON SYS.GV_$PARAMETER TO DMHS;
GRANT SELECT ON SYS.GV_$INSTANCE TO DMHS;
GRANT SELECT ON SYS.GV_$ARCHIVE_DEST TO DMHS;
GRANT SELECT ON SYS.GV_$ARCHIVE TO DMHS;
GRANT SELECT ON SYS.GV_$LOG TO DMHS;
GRANT SELECT ON SYS.GV_$LOGFILE TO DMHS;
GRANT SELECT ON SYS.DBA_TABLES TO DMHS;
GRANT SELECT ON SYS.OBJ$ TO DMHS;
GRANT SELECT ON SYS.USER$ TO DMHS;
GRANT SELECT ON SYS.COL$ TO DMHS;
GRANT SELECT ON SYS.DBA_CONS_COLUMNS TO DMHS;
GRANT SELECT ON SYS.DBA_CONSTRAINTS TO DMHS;
GRANT SELECT ON SYS.LOB$ TO DMHS;
GRANT SELECT ON SYS.TABPART$ TO DMHS;
GRANT SELECT ON SYS.TAB$ TO DMHS;
GRANT SELECT ON SYS.TABSUBPART$ TO DMHS;
GRANT SELECT ON SYS.TABCOMPART$ TO DMHS;
GRANT EXECUTE ON DBMS_FLASHBACK TO DMHS;
GRANT LOCK ANY TABLE TO DMHS;
GRANT SELECT ANY TABLE TO DMHS;
GRANT SELECT ANY DICTIONARY TO DMHS;
GRANT SELECT ON SYS.CON$ TO DMHS;
GRANT SELECT ON SYS.SEQ$ TO DMHS;
GRANT SELECT ON SYS.LOBFRAG$ TO DMHS;
GRANT SELECT ON SYS.IND$ TO DMHS;
GRANT SELECT ON SYS.ICOL$ TO DMHS;
GRANT SELECT ON SYS.CDEF$ TO DMHS;
GRANT SELECT ON SYS.CCOL$ TO DMHS;
GRANT SELECT ON SYS.PARTCOL$ TO DMHS;
GRANT SELECT ON SYS.SUBPARTCOL$ TO DMHS;
GRANT SELECT ON SYS.PARTOBJ$ TO DMHS;
GRANT SELECT ON SYS.DEFSUBPART$ TO DMHS;
GRANT SELECT ON SYS.COM$ TO DMHS;
GRANT SELECT ON SYS.COLTYPE$ TO DMHS;
GRANT SELECT ON SYS.TYPE$ TO DMHS;
GRANT SELECT ON SYS.ATTRIBUTE$ TO DMHS;
GRANT SELECT ON SYS.COLLECTION$ TO DMHS;
GRANT SELECT ON SYS.IDNSEQ$ TO DMHS;
GRANT SELECT ON SYS.DEFERRED_STG$ TO DMHS;
GRANT SELECT ON SYS.SUM$ TO DMHS;
GRANT SELECT ON SYS.SNAP$ TO DMHS;
GRANT SELECT ON SYS.MLOG$ TO DMHS;
GRANT SELECT ON SYS.MLOG_REFCOL$ TO DMHS;
GRANT SELECT ON SYS.SCHEDULER$_JOB TO DMHS;
GRANT SELECT ON SYS.RECYCLEBIN$ TO DMHS;
GRANT SELECT ON SYS.NTAB$ TO DMHS;
GRANT SELECT ON SYS.EXTERNAL_TAB$ TO DMHS;
GRANT SELECT ON SYS.V_$DATABASE_INCARNATION TO DMHS;
GRANT SELECT ON SYS.TRIGGER$ TO DMHS;

(2)如需开启DDL同步，需执行以下SQL

1)源端数据库必须允许DDL触发器的触发动作，即数据库参数_system_trig_enabled为TRUE或者未设置。查看该参数的命令如下：
set line 200
col name for a25
col value for a30
col describ for a70
select x.ksppinm Name,y.ksppstvl Value,x.ksppdesc Describ from x$ksppi x,x$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance') and x.indx=y.indx and x.ksppinm = '_system_trig_enabled';
2)需要在源端数据库以sys用户，在PDB中的sys模式下创建DDL触发器及DDL记录表。
3)需要日志捕获模块对ddl_mask进行设置。例如<ddl_mask>op:obj<ddl_mask>。
--dmhs_ddl.sql脚本如下：
CREATE TABLE DMHS_DDL_SQL (
OBJID NUMBER,
DATAOBJ NUMBER,
OP_TYPE VARCHAR2(32),
OBJ_SCHNAME VARCHAR2(128),
OBJ_NAME VARCHAR2(128),
OBJ_TYPE VARCHAR2(32),
OP_SQL VARCHAR(4000),
OP_SQL2 CLOB,
DDL_TIME DATE,
RESVD1 NUMBER,
RESVD2 NUMBER,
RESVD3 NUMBER,
RESVD4 NUMBER,
RESVD5 VARCHAR2(1000),
RESVD6 VARCHAR2(1000)
);

create or replace trigger dmhs_trigger
before ddl
on database
declare  
   e1          exception;
   sql_text    ora_name_list_t;
   ddl_sql     clob;
   op_no       number := NULL;
   objid       number := NULL;
   dataobj     number := NULL;
   v_num       number;
   sql_item    varchar(8000);
   sql_temp    varchar(8000);
begin

if (SUBSTR(ora_dict_obj_name, 1, 5) = 'DMHS_' and ora_dict_obj_name<>'DMHS_TRXID_TABLE') or (ora_dict_obj_owner = 'SYS' and ora_dict_obj_type != 'TABLESPACE') then
      if ora_dict_obj_name = 'DMHS_DDL_SQL' and ora_dict_obj_type = 'TABLE' and ora_sysevent = 'DROP' then
      raise_application_error(-20002, 'table cannot drop before dmhs_trigger is droped');
    end if;
  
       if ora_sysevent != 'TRUNCATE' or ora_dict_obj_name <> 'DMHS_DDL_SQL' then
       return;
    end if;
end if;

sql_temp := '';
dbms_lob.createtemporary(ddl_sql, TRUE);
v_num := ora_sql_txt (sql_text);
for i in 1 .. v_num
loop  
    sql_item := sql_text (i);
    if length(sql_item) + length(sql_temp) > 3000 then
        dbms_lob.append(ddl_sql, sql_temp);
        sql_temp := '';
    end if;
    sql_temp := sql_temp || sql_item;
end loop; 
dbms_lob.append(ddl_sql, sql_temp);
if ora_sysevent != 'CREATE' then
  if ora_dict_obj_type = 'TABLE' or ora_dict_obj_type = 'SEQUENCE' then
    select o.obj#, o.dataobj# into objid, dataobj from sys.obj$ o, sys.user$ u where o.name = ora_dict_obj_name and o.type# in(2, 6) and o.subname is null and u.name = ora_dict_obj_owner and o.owner# = u.user#;
  end if;
 
  insert into dmhs_ddl_sql(objid, dataobj, op_type, obj_schname, obj_name, obj_type, op_sql, ddl_time) values(objid, dataobj, ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, SUBSTR(ddl_sql, 1, 2000), sysdate);
else
  if ora_dict_obj_type = 'TABLE' then
    insert into dmhs_ddl_sql(op_type, obj_schname, obj_name, obj_type, op_sql, op_sql2, ddl_time) values(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, SUBSTR(ddl_sql, 1, 2000), ddl_sql, sysdate); 
  elsif ora_dict_obj_type in ('VIEW', 'MATERIALIZED VIEW', 'TRIGGER', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY', 'SYNONYM', 'TABLESPACE', 'USER', 'ROLE', 'INDEX', 'SEQUENCE') then
    insert into dmhs_ddl_sql(op_type, obj_schname, obj_name, obj_type, op_sql, op_sql2, ddl_time) values(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, SUBSTR(ddl_sql, 1, 2000), ddl_sql, sysdate); 
    end if;
end if;  
exception
        when e1 then raise e1;
    when no_data_found then
        dbms_output.put_line('object is not exist');
end dmhs_trigger;
/

--SELECT * FROM SYS.USER_ERRORS A WHERE A.NAME = UPPER('DMHS_TRIGGER');
--1)关闭数据回收站
SHOW PARAMETER RECYCLE
ALTER SYSTEM SET RECYCLEBIN=OFF DEFERRED;
--授权
GRANT ALL ON SYS.DMHS_DDL_SQL TO DMHS;
GRANT SELECT ANY TABLE TO DMHS;
GRANT SELECT ANY DICTIONARY TO DMHS;
GRANT CREATE SESSION TO DMHS;
GRANT LOCK ANY TABLE TO DMHS;
GRANT EXECUTE ON DBMS_FLASHBACK TO DMHS;
GRANT FLASHBACK ANY TABLE TO DMHS;