oracle 容灾库日常维护 ,健康检查脚本 以及常见问题分析
select DEST_ID, APPLIED_SCN FROM v$archive_dest
select * from v$dataguard_status;
SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;
1.日常维护
shutdown immediate;
alter database mount standby database;
alter database open read only;
alter database recover managed standby database cancel;-- 取消日志应用
alter database open read only;
alter database mount standby database;
alter database recover managed standby database disconnect from session;-- 启动日志应用
-查看应用日志延迟时间:
select value from v$dataguard_stats
where name='apply lag';
-查看接收日志延迟时间:
select value from v$dataguard_stats
where name='transport lag';
-查看主库归档
Primary: SQL> select thread#,
max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database
vdb
where val.resetlogs_change# =
vdb.resetlogs_change#
group by thread# order by 1;
-查看备库已接收归档
PhyStdby:SQL> select thread#,
max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database
vdb
where val.resetlogs_change# =
vdb.resetlogs_change#
group by thread# order by 1;
-查看备库已应用归档
PhyStdby:SQL>select thread#,
max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database
vdb
where val.resetlogs_change# =
vdb.resetlogs_change#
and val.applied in
('YES','IN-MEMORY')
group by thread# order by 1;
-查看归档应用详细情况
select first_time,sequence#,applied
from v$archived_log;
-查看主备库GAP
select * from v$archive_gap;
SQL> recover managed standby database cancel;
Media recovery complete.
startup mount;
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select open_mode,database_role,log_mode,flashback_on from v$database;
alter database commit to switchover to physical standby;
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
shutdown immediate;
startup
alter system switch logfile;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;
alter database recover managed standby database finish;
alter database recover managed standby database finish force;
FAL_CLIENT和FAL_SERVER是配置dataguard用到的两个参数,FAL指获取归档日志(Fetch Archived Log)
在一定的条件下,或者因为网络失败,或者因为资源紧张,会在primary和standby之间产生裂隙,也就是有些归档日志没有及时的传输并应用到standby库。因为MRP(managed recovery process)/LSP(logical standby process)没有与primary直接通讯的能力来获取丢失的归档日志。因此这些gaps通过FAL客户和服务器来解决,由初始化参数定义FAL_CLIENT和FAL_SERVER。
FAL_SERVER指定一个Oracle Net service name,standby数据库使用这个参数连接到FAL server,这个参数适用于standby站点。
比如,FAL_SERVER = PrimaryDB,此处PrimaryDB是一个TNS name,指向primary库。
FAL_CLIENT指定一个FAL客户端的名字,以便FAL Server可以引用standby库,这也是一个TNS name,primary库必须适当配置此TNS name指向stanby库。这个参数也是在standby库端设置。比如,
FAL_CLIENT = StandbyDB,StandbyDB是standby库的TNS name。
FAL_CLIENT和FAL_SERVER应该成对设置或改变。
这两个参数只需在standby库设置,但也可以在primary库设置这两个参数,以方便switchover或failover时primary库转变为standby角色。
alter system set fal_server='db1-old,db2-old';
ALTER SYSTEM SET fal_client='db2-1-old' SID='db2';
【Oracle官方文档中对Real-Time Apply介绍】
If the real-time apply feature is enabled, apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.
Use the ALTER DATABASE statement to enable the real-time apply feature, as follows:
For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.
转自:http://blog.csdn.net/robbie1314520/article/details/8267283
2. 检查脚本 check script:
Primary Site Script
===============================================================================
-- This script is to be run on the Primary of a Data Guard Physical Standby Site
set echo off
set feedback off
column timecol new_value tstamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol from sys.dual;
column output new_value dbname
select value || '_' output from v$parameter where name = 'db_name';
-- Output the results to this file
spool dg_Primary_diag_&&dbname&&tstamp
set lines 132
set pagesize 500
set numformat 999999999999999
set trim on
set trims on
-- Get the current Date
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set feedback on
select systimestamp from dual;
-- Primary Site Details
set heading off
set feedback off
select 'Primary Site Details' from dual;
select '********************' from dual;
set heading on
set feedback on
col db_unique_name format a15
col flashb_on format a10
select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;
-- Current SCN - this value on the primary and standby sites where real time apply is in place should be nearly the same
set heading off
set feedback off
select 'Primary Site last generated SCN' from dual;
select '*******************************' from dual;
set heading on
set feedback on
select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;
set heading off
set feedback off
select 'Standby Site last applied SCN' from dual;
select '*****************************' from dual;
set heading on
set feedback on
select DEST_ID, APPLIED_SCN FROM v$archive_dest WHERE TARGET='STANDBY';
-- Incarnation Information
--
set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on
select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;
-- Archivelog Destination Details
--
set heading off
set feedback off
select 'Archive Destination Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
-- Current Archive Locations
--
column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;
column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999
select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE
from v$archive_dest
where DESTINATION IS NOT NULL;
column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';
set heading off
set feedback off
select 'Archive Destination Errors' from dual;
select '**************************' from dual;
set heading on
set feedback on
column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest
where DESTINATION IS NOT NULL;
column message format a80
select MESSAGE, TIMESTAMP
from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;
-- Redo Log configuration
-- The size of the standby redo logs must match exactly the size on the online redo logs
set heading off
set feedback off
select 'Data Guard Redo Log Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;
select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;
-- Data Guard Parameters
--
set heading off
set feedback off
select 'Data Guard Related Parameters' from dual;
select '*****************************' from dual;
set heading on
set feedback on
column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;
-- Redo Shipping Progress
set heading off
set feedback off
select 'Data Guard Redo Shipping Progress' from dual;
select '*********************************' from dual;
set heading on
set feedback on
select systimestamp from dual;
column client_pid format a10
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
host sleep 10
select systimestamp from dual;
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
host sleep 10
select systimestamp from dual;
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
set heading off
set feedback off
select 'Data Guard Errors in the Last Hour' from dual;
select '**********************************' from dual;
set heading on
set feedback on
select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1/24;
spool off
================================================================================
Standby Site Script
================================================================================
-- This script is to be run on the Standby of a Data Guard Physical Standby Site
set echo off
set feedback off
column timecol new_value tstamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol from sys.dual;
column output new_value dbname
select value || '_' output from v$parameter where name = 'db_name';
-- Output the results to this file
spool dg_Standby_diag_&&dbname&&tstamp
set lines 132
set pagesize 500
set numformat 999999999999999
set trim on
set trims on
-- Get the current Date
set feedback on
select systimestamp from dual;
-- Standby Site Details
set heading off
set feedback off
select 'Standby Site Details' from dual;
select '********************' from dual;
set heading on
set feedback on
col db_unique_name format a15
col flashb_on format a10
select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;
-- Current SCN - this value on the primary and standby sites where real time apply is in place should be nearly the same
select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;
-- Incarnation Information
--
set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on
select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;
set heading off
set feedback off
select 'Archive Destination Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
-- Current Archive Locations
--
column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;
column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999
select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE
from v$archive_dest
where DESTINATION IS NOT NULL;
column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';
set heading off
set feedback off
select 'Archive Destination Errors' from dual;
select '**************************' from dual;
set heading on
set feedback on
column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest
where DESTINATION IS NOT NULL;
column message format a80
select MESSAGE, TIMESTAMP
from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;
-- Redo Log configuration
-- The size of the standby redo logs must match exactly the size on the online redo logs
set heading off
set feedback off
select 'Data Guard Redo Log Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;
select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;
-- Data Guard Parameters
--
set heading off
set feedback off
select 'Data Guard Related Parameters' from dual;
select '*****************************' from dual;
set heading on
set feedback on
column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;
-- Managed Recovery State
set heading off
set feedback off
select 'Data Guard Apply Status' from dual;
select '***********************' from dual;
set heading on
set feedback on
select systimestamp from dual;
column client_pid format a10
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
host sleep 10
select systimestamp from dual;
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
host sleep 10
select systimestamp from dual;
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
set heading off
set feedback off
select 'Data Guard Apply Lag' from dual;
select '********************' from dual;
set heading on
set feedback on
column name format a12
column lag_time format a20
column datum_time format a20
column time_computed format a20
SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED
from V$DATAGUARD_STATS where name like 'apply lag';
-- If there is a lag remove the comment for the select below
--SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;
set heading off
set feedback off
select 'Data Guard Gap Problems' from dual;
select '***********************' from dual;
set heading on
set feedback on
select * from v$archive_gap;
set heading off
set feedback off
select 'Data Guard Errors in the Last Hour' from dual;
select '**********************************' from dual;
set heading on
set feedback on
select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1/24;
spool off
3.############### 常见问题1
https://blog.csdn.net/huangliang0703/article/details/46558995
由dg中的db_file_name_convert参数不生效想到的db_file_create
-》 就是说,如果db_file_create这个参数有值,那么就是启用了omf(oracle的一种自动文件管理方式) 它的优先级要高于convert参数设置。
oracle搭建dg过程中涉及到一个参数db_file_name_convert
这个参数在备库库参数中是成对出现的,比如主库数据文件有两个路径
+DATA和+DATAA两个路径,'+DATA/orcl/datafile/user01.dbf',‘+DATAA/ocrl/datafile/user05.dbf''
本地路径为/oradata/orcl/datafile/
此时备库该参数这样写
alter system set DB_FILE_NAME_CONVERT='+DATA/orcl/datafile/','/oradata/orcl/datafile/', '+DATAA/orcl/datafile/','/oradata/orcl/datafile/', scope=spfile;
设置完搭建测试dg备库日志同步没有问题,主库创建一个表空间,备库没有跟随生成,或者在一个其他位置$ORACLE_HOME/dbs下生成一个uname的文件
检查convert 参数没有问题,检查STANDBY_FILE_MANAGEMENT为auto
没有问题
后来查询oracle官方文档发现我的db_create_file='/u01....'
是这个参数在起作用。
就是说,如果db_file_create这个参数有值,那么就是启用了omf(oracle的一种自动文件管理方式)
它的优先级要高于convert参数设置。
另外在备库正常运行的过程中STANDBY_FILE_MANAGEMENT参数是auto;如果是维护状态要改为mananul,比如上面我要删掉那个uname的文件。
-》db_file_name_convert 在日常运维 生效 ,主库新加数据文件,备库也根据配置 增加数据文件位置
-》log_file_name_convert 在日常运维 不生效,主库新加日志文件, 备库不会根据配置 新加日志。
只在搭建DG 环境时候,生效
分类: Oracle
2011-05-28 22:28:49
1.dataguard的主库完全备份数据库;
从库通过主库的备份恢复控制文件--->恢复控制文件时,oracle查看从库参数文件中的db_file_name_convert和log_file_name_convert参数---->将主库保存数据文件、联机日志的路径转换成从库保存数据文件、联机日志的路径---->生成从库的控制文件
###########6
https://blog.csdn.net/surperping/article/details/7902267
第一步:查看standby同步情况,通过如下语句:select sequence#,applied from v$archived_log order by sequence#;
发现好几天的归档日志都没有应用。
第二步:查看告警日志,显示如下信息:
MRP0: Background Media Recovery process shutdown (kehudb)
Sat May 28 14:10:04 2011
Completed: alter database recover managed standby database disconnect from session
Sat May 28 14:10:13 2011
alter database recover managed standby database disconnect from session
Sat May 28 14:10:13 2011
Attempt to start background Managed Standby Recovery process (kehudb)
MRP0 started with pid=23, OS id=18081
Sat May 28 14:10:13 2011
MRP0: Background Managed Standby Recovery process started (kehudb)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Sat May 28 14:10:18 2011
Errors in file /u01/oracle/admin/kehudb/bdump/kehudb_mrp0_18081.trc:
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/u01/oracle/product/10.2.0/db/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/u01/oracle/product/10.2.0/db/dbs/UNNAMED00010'
Sat May 28 14:10:18 2011
Errors in file /u01/oracle/admin/kehudb/bdump/kehudb_mrp0_18081.trc:
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/u01/oracle/product/10.2.0/db/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/u01/oracle/product/10.2.0/db/dbs/UNNAMED00010'
以上问题主要是因为:Standby_file_management参数设置STANDBY_FILE_MANAGEMENT =MANUAL造成不会自动管理数据文件,比如主库增加了数据文件,备库不会自动增加,若设置为AUTO即可实现自动管理。
可以查看备库一下:
SQL>show parameter Standby_file_management
STANDBY_FILE_MANAGEMENT =MANUAL
可以通过如下语句在备库执行:
1)调整standby_file_management参数为AUTO
SQL> alter system set standby_file_management=auto;
第三步:
在这种故障场景下,我们可以通过调整数据文件的方法在备库端进行处理,查看主库和备库数据文件对比一下是否一致。
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/oradata/twwww01.dbf
/oradata/users01.dbf
/oradata/system01.dbf
/oradata/sysaux01.dbf
/u01/oracle/product/10.2.0/db/dbs/UNNAMED00010
通过以上参数设置后,还必须做如下处理才能解决以上问题
SQL> alter database create datafile '/u01/oracle/product/10.2.0/db/dbs/UNNAMED00010' as '/oradata/twwww03.dbf';
Database altered.
#########add logfile
### primary
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database add logfile thread 1 group 4 size 512M;
alter database add logfile thread 1 group 5 size 512M;
alter database add logfile thread 1 group 6 size 512M;
alter database add standby logfile thread 1 group 7 size 512M;
alter database add standby logfile thread 1 group 8 size 512M;
alter database add standby logfile thread 1 group 9 size 512M;
alter database add standby logfile thread 1 group 10 size 512M;
alter database add standby logfile thread 1 group 11 size 512M;
alter database add standby logfile thread 1 group 12 size 512M;
alter database add standby logfile thread 1 group 13 size 512M;
########standby
备库:
首先要停掉备库的日志应用:
alter database recover managed standby database cancel;
再次,要把standby_logfile_management参数改成手动:
alter system set standby_file_management=manual;
现在就可以开始做drop和 add操作了。举例:
alter database add logfile group 4 '/data/oracle/oradata/test/redo04.log' size 300m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/data/oracle/oradata/test/stredo1.log' size 1g;
这里在删除standby redo组的时候会遇到一些特使情况;
1. 首先要清理archive logfile,然后再drop
alter database clear logfile group 4;
2,如果还是 出现问题,尝试在主库切换下日志。 alter system switch logfile;
3.此外,在clear lofile 的时候可能会报错:
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/xezf/redo02.log'
ORA报错查询:
ORA-19527:
physical standby redo log must be renamed
Cause:
The CLEAR LOGFILE command was used at a physical standby database. This command cannot be used at a physical standby database unless the LOG_FILE_NAME_CONVERT initialization parameter is set. This is required to avoid overwriting the primary database's logfiles.
Action:
Set the LOG_FILE_NAME_CONVERT initialization parameter.
我们需要设置LOG_FILE_NAME_CONVERT 参数,才能使用clear logfile命令。
SQL> show parameter NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
SQL> alter system set log_file_name_convert='/data/oradata/test','/data/oradata/test' scope=spfile;
System altered. 之后重启数据库。
再进行clear logfile 就成功了。
结束后,要还原上面设置,并开启设置开启日志应用。
alter system set standby_file_management=auto;
alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;
SQL_ID c35yvu7uzqugq, child number 0-------------------------------------INSERT /*+ append */ INTO OMLOPR.VT_INC_550( INTERNAL_KEY ,BRANCH ,CCY ,CLIENT_NO ,GLOBAL_ID_TYPE ,GLOBAL_ID ,CERTIFICATE_NO ,CLIENT_SHORT ,PROFIT_CENTRE ,ACCT_OPEN_DATE ,OPEN_TRAN_DATE ,ACCT_STATUS ,ACCT_NO ,ACCT_TYPE ,ACCT_DESC ,OWNERSHIP_TYPE ,FREE_CHEQUES ,ATA_ACCT ,DEPOSIT_TYPE ,ACTUAL_OR_LEDGER_BAL ,INT_STMT ,STMT_PBK ,PRINT_STMT ,CLIENT_IND ,DEL_FLAG ,OD_FACILITY ,RESTRAINT_PRESENT ,LEDGER_BAL ,ACTUAL_BAL ,CALC_BAL ,PREV_DAY_LEDGER_BAL ,PREV_DAY_ACTUAL_BAL ,PREV_DAY_CALC_BAL ,TOLERANCE_AMT ,TOTAL_FLOATS_AMT ,TOTAL_PLEDGED_AMT ,TOTAL_AUTH_OD ,COMPENSE_ACCT_TYPE ,COMPENSE_CTRL_ACCT ,WS_ID ,USER_ID ,OFFICER_ID ,LAST_CHANGE_OFFICER ,LAST_CHANGE_DATE ,LAST_RUN_DATE ,LAST_TRAN_DATE ,LAST_BAL_UPDATE ,LAST_PBK_UPD_BAL ,LAST_PBK_UPD_DATE ,LAST_PBK_LINE ,ACCT_CLOSE_DATE ,ACCT_CLOSE_REASON ,CATEGORY_TYPE ,CLIENT_TYPE ,DIRECT_DEBIT ,ACCT_CLOSE_OFFICER ,CASHIER_ORDER_IND ,CB_CO Plan hash value: 3269117650 ----------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |----------------------------------------------------------------------------------------------------------------------------| 0 | INSERT STATEMENT | | | | 7303 (100)| | | | || 1 | PX COORDINATOR | | | | | | | | || 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 6682 | 7303 (3)| 00:01:43 | Q1,01 | P->S | QC (RAND) || 3 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | ||* 4 | HASH JOIN | | 1 | 6682 | 7303 (3)| 00:01:43 | Q1,01 | PCWP | || 5 | PX RECEIVE | | 1 | 3341 | 2 (0)| 00:00:01 | Q1,01 | PCWP | || 6 | PX SEND BROADCAST | :TQ10000 | 1 | 3341 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST || 7 | PX BLOCK ITERATOR | | 1 | 3341 | 2 (0)| 00:00:01 | Q1,00 | PCWC | ||* 8 | TABLE ACCESS FULL| E002_SYM_RB_ACCT03 | 1 | 3341 | 2 (0)| 00:00:01 | Q1,00 | PCWP | || 9 | PX BLOCK ITERATOR | | 6157K| 19G| 7292 (3)| 00:01:43 | Q1,01 | PCWC | ||* 10 | TABLE ACCESS FULL | VT_BAK_550 | 6157K| 19G| 7292 (3)| 00:01:43 | Q1,01 | PCWP | |---------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 4 - access(NVL("N"."INTERNAL_KEY",0)=NVL("O"."INTERNAL_KEY",0)) filter((NVL("N"."BRANCH",' ')<>NVL("O"."BRANCH",' ') OR NVL("N"."CCY",' ')<>NVL("O"."CCY",' ') OR NVL("N"."CLIENT_NO",' ')<>NVL("O"."CLIENT_NO",' ') OR NVL("N"."GLOBAL_ID_TYPE",' ')<>NVL("O"."GLOBAL_ID_TYPE",' ') OR NVL("N"."GLOBAL_ID",' ')<>NVL("O"."GLOBAL_ID",' ') OR NVL("N"."CERTIFICATE_NO",' ')<>NVL("O"."CERTIFICATE_NO",' ') OR NVL("N"."CLIENT_SHORT",' ')<>NVL("O"."CLIENT_SHORT",' ') OR NVL("N"."PROFIT_CENTRE",' ')<>NVL("O"."PROFIT_CENTRE",' ') OR NVL("N"."ACCT_OPEN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."ACCT_OPEN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."OPEN_TRAN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."OPEN_TRAN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."ACCT_STATUS",' ')<>NVL("O"."ACCT_STATUS",' ') OR NVL("N"."ACCT_NO",' ')<>NVL("O"."ACCT_NO",' ') OR NVL("N"."ACCT_TYPE",' ')<>NVL("O"."ACCT_TYPE",' ') OR NVL("N"."ACCT_DESC",' ')<>NVL("O"."ACCT_DESC",' ') OR NVL("N"."OWNERSHIP_TYPE",' ')<>NVL("O"."OWNERSHIP_TYPE",' ') OR NVL("N"."FREE_CHEQUES",0)<>NVL("O"."FREE_CHEQUES",0) OR NVL("N"."ATA_ACCT",' ')<>NVL("O"."ATA_ACCT",' ') OR NVL("N"."DEPOSIT_TYPE",' ')<>NVL("O"."DEPOSIT_TYPE",' ') OR NVL("N"."ACTUAL_OR_LEDGER_BAL",' ')<>NVL("O"."ACTUAL_OR_LEDGER_BAL",' ') OR NVL("N"."INT_STMT",' ')<>NVL("O"."INT_STMT",' ') OR NVL("N"."STMT_PBK",' ')<>NVL("O"."STMT_PBK",' ') OR NVL("N"."PRINT_STMT",' ')<>NVL("O"."PRINT_STMT",' ') OR NVL("N"."CLIENT_IND",' ')<>NVL("O"."CLIENT_IND",' ') OR NVL("N"."DEL_FLAG",' ')<>NVL("O"."DEL_FLAG",' ') OR NVL("N"."OD_FACILITY",' ')<>NVL("O"."OD_FACILITY",' ') OR NVL("N"."RESTRAINT_PRESENT",' ')<>NVL("O"."RESTRAINT_PRESENT",' ') OR NVL("N"."LEDGER_BAL",0)<>NVL("O"."LEDGER_BAL",0) OR NVL("N"."ACTUAL_BAL",0)<>NVL("O"."ACTUAL_BAL",0) OR NVL("N"."CALC_BAL",0)<>NVL("O"."CALC_BAL",0) OR NVL("N"."PREV_DAY_LEDGER_BAL",0)<>NVL("O"."PREV_DAY_LEDGER_BAL",0) OR NVL("N"."PREV_DAY_ACTUAL_BAL",0)<>NVL("O"."PREV_DAY_ACTUAL_BAL",0) OR NVL("N"."PREV_DAY_CALC_BAL",0)<>NVL("O"."PREV_DAY_CALC_BAL",0) OR NVL("N"."TOLERANCE_AMT",0)<>NVL("O"."TOLERANCE_AMT",0) OR NVL("N"."TOTAL_FLOATS_AMT",0)<>NVL("O"."TOTAL_FLOATS_AMT",0) OR NVL("N"."TOTAL_PLEDGED_AMT",0)<>NVL("O"."TOTAL_PLEDGED_AMT",0) OR NVL("N"."TOTAL_AUTH_OD",0)<>NVL("O"."TOTAL_AUTH_OD",0) OR NVL("N"."COMPENSE_ACCT_TYPE",' ')<>NVL("O"."COMPENSE_ACCT_TYPE",' ') OR NVL("N"."COMPENSE_CTRL_ACCT",0)<>NVL("O"."COMPENSE_CTRL_ACCT",0) OR NVL("N"."WS_ID",' ')<>NVL("O"."WS_ID",' ') OR NVL("N"."USER_ID",' ')<>NVL("O"."USER_ID",' ') OR NVL("N"."OFFICER_ID",' ')<>NVL("O"."OFFICER_ID",' ') OR NVL("N"."LAST_CHANGE_OFFICER",' ')<>NVL("O"."LAST_CHANGE_OFFICER",' ') OR NVL("N"."LAST_CHANGE_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."LAST_CHANGE_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."LAST_RUN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."LAST_RUN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."LAST_TRAN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."LAST_TRAN_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."LAST_BAL_UPDATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."LAST_BAL_UPDATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."LAST_PBK_UPD_BAL",0)<>NVL("O"."LAST_PBK_UPD_BAL",0) OR NVL("N"."LAST_PBK_UPD_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."LAST_PBK_UPD_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."LAST_PBK_LINE",0)<>NVL("O"."LAST_PBK_LINE",0) OR NVL("N"."ACCT_CLOSE_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<>NVL("O"."ACCT_CLOSE_DATE",TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR NVL("N"."ACCT_CLOSE_REASON",' ')<>NVL("O"."ACCT_CLOSE_REASON",' ') OR NVL("N"."CATEGORY_TYPE",' ')<>NVL("O"."CATEGORY_TYPE",' ') OR NVL("N"."CLIENT_TY) 8 - access(:Z>=:Z AND :Z<=:Z) 10 - access(:Z>=:Z AND :Z<=:Z) Note----- - dynamic sampling used for this statement (level=2)
最新文章
- vs2015 生成项目时,提示执行失败,参数错误
- mybatis的一些小总结
- 实验1_IPv6地址配置
- 关于apache httpd.conf脚本的理解
- ember
- mysql TRUNCATE
- [LeetCode 115] - 不同子序列(Distinct Subsequences)
- IOS 下雪动画修改版本
- Flask web开发 请求拦截和预处理
- 使用Maven来创建WEB项目
- Vue(day2)
- [20180603]Python读写csv
- C#编辑EXE使用的appSettings节点的Config文件
- android 获取图库中展示到界面中
- Docker安装ActiveMQ
- 关于YARN Node Labels的一点理解
- 【Webpack2.X笔记】 配合react项目进行配置
- Oracle管理监控之oracle用户管理方法
- 使用Maven部署构件至私服
- # 2017-2018-1 20155302 课下实践IPC及课上补充