expdp中PARALLEL和DUMPFILE关系

今天有朋友在说PARALLEL设置为n(大于1),DUMPFILE为一个文件(不包含%U),会出现什么样的情况。下面通过实验来说明这个问题
1.当并发数比较大时(这里实验使用4),expdp会报ORA-39095错误

[oracle@node1 tmp]$ expdp chf/xifenfei DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=4

Export: Release 11.2.0.3.0 - Production on Wed Mar 7 20:48:52 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=4 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.362 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

2.并发数较少时(实验为2),通过attach观察
2.1)执行expdp导出

[oracle@node1 tmp]$ expdp chf/xifenfei DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=2

Export: Release 11.2.0.3.0 - Production on Wed Mar 7 20:49:15 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_02":  chf/******** DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=2 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.362 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "CHF"."T1"                                  4.647 GB 51080958 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "CHF"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_02 is:
  /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp
Job "CHF"."SYS_EXPORT_TABLE_02" successfully completed at 20:52:23

2.2)在导出过程中,使用attach观察expdp工作情况

[oracle@node1 trace]$ expdp chf/xifenfei attach=SYS_EXPORT_TABLE_02

Export: Release 11.2.0.3.0 - Production on Wed Mar 7 20:49:38 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Job: SYS_EXPORT_TABLE_02
  Owner: CHF                            
  Operation: EXPORT                         
  Creator Privs: TRUE                           
  GUID: BAA7964815021C96E0438C09A8C04319
  Start Time: Wednesday, 07 March, 2012 20:49:16
  Mode: TABLE                          
  Instance: chf
  Max Parallelism: 2
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        chf/******** DUMPFILE=xifenfei.dmp TABLES=t1 PARALLEL=2 
  State: EXECUTING                      
  Bytes Processed: 0
  Current Parallelism: 2
  Job Error Count: 0
  Dump File: /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp
    bytes written: 4,096
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: CHF
  Object Name: T1
  Object Type: TABLE_EXPORT/TABLE/TABLE
  Completed Objects: 1
  Total Objects: 1
  Worker Parallelism: 1
  
Worker 2 Status:
  Process Name: DW02
  State: EXECUTING                      
  Object Schema: CHF
  Object Name: T1
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 16,104,279
  Worker Parallelism: 1

Export> status

Job: SYS_EXPORT_TABLE_02
  Operation: EXPORT                         
  Mode: TABLE                          
  State: EXECUTING                      
  Bytes Processed: 0
  Current Parallelism: 2
  Job Error Count: 0
  Dump File: /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp
    bytes written: 4,096
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: CHF
  Object Name: T1
  Object Type: TABLE_EXPORT/TABLE/TABLE
  Completed Objects: 1
  Total Objects: 1
  Worker Parallelism: 1
  
Worker 2 Status:
  Process Name: DW02
  State: EXECUTING                      
  Object Schema: CHF
  Object Name: T1
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 35,425,534
  Worker Parallelism: 1

Export> status

Job: SYS_EXPORT_TABLE_02
  Operation: EXPORT                         
  Mode: TABLE                          
  State: COMPLETING                     
  Bytes Processed: 4,989,989,105
  Percent Done: 100
  Current Parallelism: 2
  Job Error Count: 0
  Dump File: /opt/oracle/diag/rdbms/chf/chf/trace/xifenfei.dmp
    bytes written: 4,990,009,344
  
Worker 1 Status:
  Process Name: DW00
  State: WORK WAITING                   
  
Worker 2 Status:
  Process Name: DW02
  State: WORK WAITING                   

3.最终结论
3.1)如果并发设置过大,在导出过程中直接报错
3.2)如果导出文件数量少于并发数时,多于并发将不会工作。

发表在 逻辑备份/恢复 | 评论关闭

LNNVL函数使用

LNNVL官方解释
LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, even in contexts where the IS [NOT] NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.

LNNVL官方解释翻译
lnnvl用于某个语句的where子句中的条件,如果条件为true就返回false;如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。

模拟测试环境

SQL> create table xifenfei(name varchar2(20),year number);

Table created.

SQL> insert into xifenfei values('xifenfei2001',2001);

1 row created.

SQL> insert into xifenfei values('xifenfei2002',2002);

1 row created.

SQL> insert into xifenfei values('xifenfei2003',2003);

1 row created.

SQL> insert into xifenfei values('xifenfei2004',2004);

1 row created.

SQL> insert into xifenfei values('xifenfei2005',2005);

1 row created.

SQL> insert into xifenfei values('xifenfei2006',2006);

1 row created.

SQL> insert into xifenfei values('xifenfei2007',2007);

1 row created.

SQL> insert into xifenfei values('xifenfei2008',null);

1 row created.

SQL> insert into xifenfei values('xifenfei2009',2009);

1 row created.

SQL> insert into xifenfei values('xifenfei2010',2010);

1 row created.

SQL> insert into xifenfei values('xifenfei2011',2011);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xifenfei;

NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

11 rows selected.

几种情况测试说明

--年份小于2009(lnnvl表示年份大于或者2009包含null)
SQL> select * from xifenfei where lnnvl(year<2009);

NAME                       YEAR
-------------------- ----------
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

--year不为null(lnnvl表示年份为null)
SQL> select * from xifenfei where lnnvl(year is not null);

NAME                       YEAR
-------------------- ----------
xifenfei2008

--年份为null(lnnvl表示年份不为null)
SQL> select * from xifenfei where lnnvl(year is  null);

NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

10 rows selected.

--年份为12345(lnnvl表示年份不为12345)
SQL> select * from xifenfei where lnnvl(year =12345);

NAME                       YEAR
-------------------- ----------
xifenfei2001               2001
xifenfei2002               2002
xifenfei2003               2003
xifenfei2004               2004
xifenfei2005               2005
xifenfei2006               2006
xifenfei2007               2007
xifenfei2008
xifenfei2009               2009
xifenfei2010               2010
xifenfei2011               2011

11 rows selected.

--年份不为12345(lnnvl表示年份为12345或者null)
SQL> select * from xifenfei where lnnvl(year !=12345);

NAME                       YEAR
-------------------- ----------
xifenfei2008
发表在 Oracle 开发 | 评论关闭

Configuring an active standby pair with one subscriber

Step 1: Create the DSNs for the master and the subscriber databases

[master1]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate//master1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

[master2]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate//master2
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

[subscriber1]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate/subscriber1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

Step 2: Create a table in one of the master databases

[oracle@xifenfei info]$ ttIsql master1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master1";
Connection successful: DSN=master1;UID=oracle;DataStore=/u01/TimesTen/replicate//master1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CREATE TABLE tab (a NUMBER NOT NULL,
       >    b CHAR(18),
       >    PRIMARY KEY (a));

Step 3: Define the active standby pair

Command>  CREATE ACTIVE STANDBY PAIR master1, master2
       >    SUBSCRIBER subscriber1;

Step 4: Start the replication agent on a master database

Command>  CALL ttRepStart;

Step 5: Set the state of a master database to ‘ACTIVE’

Command>  CALL ttRepStateSet('ACTIVE');

Step 6. Create a user on the active database

Command> CREATE USER terry IDENTIFIED BY terry;

User created.

Command>  GRANT admin TO terry;

Step 7: Duplicate the active database to the standby database

[oracle@xifenfei info]$ ttRepAdmin -duplicate -from master1 -host xifenfei -uid terry -pwd terry  "dsn=master2"

Step 8: Start the replication agent on the standby database

[oracle@xifenfei info]$ ttIsql master2

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


connect "DSN=master2";
Connection successful: DSN=master2;UID=oracle;DataStore=/u01/TimesTen/replicate//master2;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CALL ttRepStart;

Step 9. Duplicate the standby database to the subscriber

[oracle@xifenfei info]$ ttRepAdmin -duplicate -from master2 -host xifenfei -uid terry -pwd terry  "dsn=subscriber1"

Step 10: Start the replication agent on the subscriber

[oracle@xifenfei info]$ ttIsql subscriber1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=subscriber1";
Connection successful: DSN=subscriber1;UID=oracle;DataStore=/u01/TimesTen/replicate/subscriber1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CALL ttRepStart;

Step 11: Insert data into the table on the active database

[oracle@xifenfei info]$ ttIsql master1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master1";
Connection successful: DSN=master1;UID=oracle;DataStore=/u01/TimesTen/replicate//master1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> INSERT INTO tab VALUES (1,'Hello');
1 row inserted.
Command> commit;

Setp 12:Verify that the insert is replicated to master2 and subscriber1

[oracle@xifenfei info]$ ttIsql master2

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master2";
Connection successful: DSN=master2;UID=oracle;DataStore=/u01/TimesTen/replicate//master2;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from tab;
< 1, Hello              >
1 row found.
Command> exit
Disconnecting...
Done.
[oracle@xifenfei info]$ ttIsql subscriber1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=subscriber1";
Connection successful: DSN=subscriber1;UID=oracle;DataStore=/u01/TimesTen/replicate/subscriber1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from tab;
< 1, Hello              >
1 row found.

Configuring an active standby pair with one subscriber

发表在 TimesTen | 3 条评论