1. 问题描述
Oracle DataGuard日志传输失败，日志信息如下所示，
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
FAL[client, ARC2]: Error 16191 connecting to epmsn for fetching...
环境：Oracle 11.2.0.4
采用并行的方式，快速收集全库统计信息，多用于跨版本升级之后，对全库的统计信息重新进行快速收集：
123456789101112131415
--开启计时set timing on --设置并行收集exec dbms_stats.set_global_prefs('CONCURRENT','TRUE'); --开始收集全库统计信息begindbms_stats.gather_database_stats(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade=>true, degree=>120);end;/ --关闭...
count 函数大家都希望返回 >=0 的整数，当查询条件中使用 group by 分组的时候，如果分组结果为空候会返回空 null
即便使用 IFNULL(COUNT(*),0) 结果扔为 null，解决办法是可以使用 case 判断 null 来返回 0
1
CASE WHEN (count(*) IS NULL) THEN 0 ELSE count(*) END total
涉及到的表
TTRD_OTC_TRADE
首先需要确认数据库已经开启归档模式
数据库需要执行的语句总结如下
-- 为捕获的数据库启用补充日志记录,以便数据更改捕获更改的数据库行之前的状态,下面说明了如何在数据库级别进行配置。
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- 修改TREALDEAL表让其支持增量日志
ALTER TABLE TTRD_OTC_TRADE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
-- 给用户赋权限
GRANT RESOURCE,EXECUTE_CATALOG_ROLE,SELECT_CATALOG_ROLE TO datacenter;
GRANT SELECT ANY TRANSACTION,SELECT ANY DICTIONARY,LOCK ANY TAB...
12345678910111213141516171819202122232425262728293031
# -*- coding: utf-8 -*-import cx_Oracleimport datetimeimport os#设置中文环境os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'#连接ORACLE数据库DB=cx_Oracle.connect('sys_monitor','sys_monitor','10.10.10.10:1521/orcl')print DB.dsnprint DB.version#创建游标cursor = DB.cursor()#定义入参a_d_bdate = datetime.datetime.strptime('2022-12-01','%Y-%m-%d')a_d_edate = datetime.datetime.str...
注意：不是每次执行的语句都会记录（如果执行的语句是能在该表找到的则ORACLE不会再次记录，就是说本次执行的语句和上次或者说以前的语句一模一样则下面语句就查不出来的）：
12345
select last_load_time, sql_text from v$sql where module not in ('jsenv.exe','w3wp.exe','SQL Developer') --and sql_text like '%"Extent1"."ENCOUNTERID"%' order by last_load_time desc;select sql_text,last_load_time from v$sql order by last_load_time desc;SELECT...
创建并设置新的undo表空间
create undo tablespace undotbs02datafile '/home/oradata/mydb/undotbs201.dbf' size 1500M reuse AUTOEXTEND ON;
alter system set undo_tablespace=undotbs02;
select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
删除原UNDO表空间
alter tablespace undotbs01 offline
drop tablespace undotbs1 including contents and datafiles;
表空间增加数据文件方法
ALTER TABLESPACE undotbs02 ADD DATAFILE '/home/oradata/mydb/undotbs202.dbf'...
查询11g表空间使用率：
12345678910111213141516171819202122232425262728293031
set linesize 200 pagesize 999 col tablespace_name for a30 col file_name for a65 select name inst_name, b.tablespace_name , ROUND(b.total/1024/1024/1024 ,4) total_GB , ROUND(b.maxtotal/1024/1024/1024,4) maxtotal_GB, ROUND(a.free/1024/1024/1024,4) free_GB, ROUND(...
123456789101112131415
create or replace trigger logon_ip_controlAFTER logon on databasedeclare ip STRING(30); prod_owner STRING(30);BEGIN SELECT SYS_CONTEXT('USERENV','SESSION_USER') into prod_owner from dual; SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip from dual; if prod_owner='PROD_C' or prod_owner='PROD_S' ...
123456789101112131415161718192021222324252627282930313233343536373839404142
set verify offcol value format a20column file_name format a50 word_wrappedcolumn smallest format 999,990 heading "Smallest|Size|Poss."column currsize format 999,990 heading "Current|Size"column savings format 999,990 heading "Poss.|Savings"break on reportcompute sum of savings on report column value new_val blksizeselect value from v$parameter where name ...
