Oracle数据泵导入导出

首先要说明一下,数据泵只可以在服务端运行,而且数据泵要用到DIRECTORY对象。

一、新建DIRECTORY并授权

#创建DIRECTORY
CREATE DIRECTORY dump_dir AS 'DIRECTORY_FULL_PATH';

#授权
GRANT READ, WRITE ON DIRECTORY dump_dir TO user_id;

二、数据泵导出数据

#导出表
expdp user_id/user_pwd directory=dump_dir dumpfile=table01.dmp tables=table01 logfile=table01.log

#导出方案
expdp user_id/user_pwd directory=dump_dir dumpfile=schema01.dmp schemas=schema01 logfile=schema01.log

#导出表空间
expdp user_id/user_pwd directory=dump_dir dumpfile=tbs01.dmp tablespaces=tbs01 logfile=tbs01.log

#导出数据库
expdp user_id/user_pwd directory=dump_dir dumpfile=db01.dmp full=y logfile=db01.log

三、数据泵导入数据

#导入表
impdp user_id/user_pwd directory=dump_dir dumpfile=table01.dmp tables=table01 

#导入方案
impdp user_id/user_pwd directory=dump_dir dumpfile=schema01.dmp schemas=schema01

#导入表空间
impdp user_id/user_pwd directory=dump_dir dumpfile=tbs01.dmp tablespaces=tbs01

#导入数据库
impdp user_id/user_pwd directory=dump_dir dumpfile=db01.dmp full=y

Oracle与SQL Server同步技术对比

方式 Oracle SQL Server 说明
备份还原 备份还原 备份还原 简单粗暴,无法实时,无法实现增量
日志备份 备库(Dataguard) 数据库镜像(Database Mirroring)
日志传输(Log Shipping)
读写操作受限
集群 RAC(Real Application Clusters) 集群(Database Cluster) RAC配置复杂,SQL Server集群只有单节点工作。实际存储只有一份。
视图 物化视图(Materialized View) 索引视图(Indexed Views) 不可改表结构,如增加字段等。
数据变更捕获 CDC(Change Data Capture) CDC(Change Data Capture) 不够灵活,无法配置只想获取部分事件,数据量很大。
订阅发布 ogg(Oracle Golden Gate)
流复制(Stream Replication)
高级复制(Oracle advanced Replication)
订阅发布(Publish and Subscribe)
数据库复制(Database Replication)
订阅发布(Publish and Subscribe)
最灵活的方式了,但也有限制。如果ogg在源加一列,或订阅发布的快照过期了,就惨了

eXistDB简单Tirgger示例03

  • XCONF文件中指定XQuery文件路径
  • XCONF文件中包含XQuery文件
  • XCONF文件中指定Java类

第三种方式,是用XCONF文件通知eXistDB要对哪个collection中的哪些操作做触发,然后触发器指向一个JAVA类。

1、首先,编写触发器的java类,打成jar包,放到%existdb_home%\lib\user路径下
TriggerTest.java

package com.neohope.existdb.test;


import org.exist.collections.Collection;
import org.exist.collections.IndexInfo;
import org.exist.collections.triggers.DocumentTrigger;
import org.exist.collections.triggers.SAXTrigger;
import org.exist.collections.triggers.TriggerException;
import org.exist.dom.DocumentImpl;
import org.exist.dom.NodeSet;
import org.exist.security.PermissionDeniedException;
import org.exist.security.xacml.AccessContext;
import org.exist.storage.DBBroker;
import org.exist.storage.txn.Txn;
import org.exist.xmldb.XmldbURI;
import org.exist.xquery.CompiledXQuery;
import org.exist.xquery.XPathException;
import org.exist.xquery.XQueryContext;

import java.util.ArrayList;
import java.util.Map;

public class TriggerTest extends SAXTrigger implements DocumentTrigger {

    private String logCollection = "xmldb:exist:///db/Triggers";
    private String logFileName = "logj.xml";
    private String logUri;

    @Override
    public void configure(DBBroker broker, Collection parent, Map parameters)
            throws TriggerException {
        super.configure(broker, parent, parameters);

        ArrayList<String> objList =  (ArrayList<String>)parameters.get("LogFileName");
        if(objList!=null && objList.size()>0)
        {
            logFileName= objList.get(0);
        }

        logUri = logCollection+"/"+logFileName;
    }

    @Override
    public void beforeCreateDocument(DBBroker broker, Txn transaction, XmldbURI uri) throws TriggerException {
        LogEvent(broker,uri.toString(),"beforeCreateDocument");
    }

    @Override
    public void afterCreateDocument(DBBroker broker, Txn transaction, DocumentImpl document) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"afterCreateDocument");
    }

    @Override
    public void beforeUpdateDocument(DBBroker broker, Txn transaction, DocumentImpl document) throws TriggerException {
       LogEvent(broker,document.getDocumentURI(), "beforeUpdateDocument");
    }

    @Override
    public void afterUpdateDocument(DBBroker broker, Txn transaction, DocumentImpl document) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"afterUpdateDocument");
    }

    @Override
    public void beforeMoveDocument(DBBroker broker, Txn transaction, DocumentImpl document, XmldbURI newUri) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"beforeMoveDocument");
    }

    @Override
    public void afterMoveDocument(DBBroker broker, Txn transaction, DocumentImpl document, XmldbURI newUri) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"afterMoveDocument");
    }

    @Override
    public void beforeCopyDocument(DBBroker broker, Txn transaction, DocumentImpl document, XmldbURI newUri) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"beforeCopyDocument");
    }

    @Override
    public void afterCopyDocument(DBBroker broker, Txn transaction, DocumentImpl document, XmldbURI newUri) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"afterCopyDocument");
    }

    @Override
    public void beforeDeleteDocument(DBBroker broker, Txn transaction, DocumentImpl document) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"beforeDeleteDocument");
    }

    @Override
    public void afterDeleteDocument(DBBroker broker, Txn transaction, XmldbURI uri) throws TriggerException {
       LogEvent(broker, uri.toString(),"afterDeleteDocument");
    }

    @Override
    public void beforeUpdateDocumentMetadata(DBBroker broker, Txn txn, DocumentImpl document) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"beforeUpdateDocumentMetadata");
    }

    @Override
    public void afterUpdateDocumentMetadata(DBBroker broker, Txn txn, DocumentImpl document) throws TriggerException {
       LogEvent(broker, document.getDocumentURI(),"afterUpdateDocumentMetadata");
    }

    private void LogEvent(DBBroker broker,String uriFile, String logContent) throws TriggerException {
        String  xQuery = "update insert <trigger event=\""+logContent+"\" uri=\""+uriFile+"\" timestamp=\"{current-dateTime()}\"/> into doc(\""+logUri+"\")/TriggerLogs";

        try {
            XQueryContext  context  = broker.getXQueryService().newContext(AccessContext.TRIGGER);
            CreateLogFile(broker,context);
            CompiledXQuery compiled = broker.getXQueryService().compile(context,xQuery);
            broker.getXQueryService().execute(compiled, NodeSet.EMPTY_SET);
        } catch (XPathException e) {
            e.printStackTrace();
        } catch (PermissionDeniedException e) {
            e.printStackTrace();
        }
    }

    private void CreateLogFile(DBBroker broker,XQueryContext  context)
    {
        String  xQuery = "if (not(doc-available(\""+logUri+"\"))) then xmldb:store(\""+logCollection+"\", \""+logFileName+"\", <TriggerLogs/>) else ()";

        try {
            CompiledXQuery compiled = broker.getXQueryService().compile(context,xQuery);
            broker.getXQueryService().execute(compiled, NodeSet.EMPTY_SET);
        } catch (XPathException e) {
            e.printStackTrace();
        } catch (PermissionDeniedException e) {
            e.printStackTrace();
        }
    }
}

Continue reading eXistDB简单Tirgger示例03

eXistDB简单Tirgger示例02

  • XCONF文件中指定XQuery文件路径
  • XCONF文件中包含XQuery文件
  • XCONF文件中指定Java类

第二种方式,是用XCONF文件通知eXistDB要对哪个collection中的哪些操作做触发,然后将XQuery语句包含在XCONF文件中。

1、在你需要触发的collection的对应配置collection中,增加一个xconf文件,文件名任意,官方推荐collection.xconf。配置collection与原collection的对应关系为,在/db/system/config/db下,建立/db下相同的collection。
比如,如果你希望监控/db/cda02路径,就需要在/db/system/config/db/cda02路径下,新增一个collection.xconf。
collection.xconf

<collection xmlns="http://exist-db.org/collection-config/1.0">
    <triggers>
        <trigger event="create" class="org.exist.collections.triggers.XQueryTrigger">
            <parameter name="query" value="
             xquery version '3.0';

             module namespace trigger='http://exist-db.org/xquery/trigger';
             declare namespace xmldb='http://exist-db.org/xquery/xmldb';

             declare function trigger:before-create-document($uri as xs:anyURI)
             {
                 local:log-event('before', 'create', 'document', $uri)
             };

             declare function trigger:after-create-document($uri as xs:anyURI)
             {
                 local:log-event('after', 'create', 'document', $uri)
             };
             
             declare function trigger:before-delete-document($uri as xs:anyURI)
             {
                 local:log-event('before', 'delete', 'document', $uri)
             };
             
             declare function trigger:after-delete-document($uri as xs:anyURI)
             {
                 local:log-event('after', 'delete', 'document', $uri)
             };
             
             declare function local:log-event($type as xs:string, $event as xs:string, $object-type as xs:string, $uri as xs:string)
             {
                 let $log-collection := '/db/Triggers'
                 let $log := 'log02.xml'
                 let $log-uri := concat($log-collection, '/', $log)
                 return
                 (
                     (: util:log does not work at all
                         util:log('warn', 'trigger fired'),
                     🙂
                     
                     (: create the log file if it does not exist 🙂
                     if (not(doc-available($log-uri))) then
                         xmldb:store($log-collection, $log, &lt;triggers/&gt;)
                     else ()
                     ,
                     (: log the trigger details to the log file 🙂
                     update insert &lt;trigger event='{string-join(($type, $event, $object-type), '-')}' uri='{$uri}' timestamp='{current-dateTime()}'/&gt; into doc($log-uri)/triggers                 )             };"/>
        </trigger>
    </triggers>
</collection>

Continue reading eXistDB简单Tirgger示例02

eXistDB简单Tirgger示例01

  • XCONF文件中指定XQuery文件路径
  • XCONF文件中包含XQuery文件
  • XCONF文件中指定Java类

第一种方式,是用XCONF文件通知eXistDB要对哪个collection中的哪些操作做触发,然后触发器指向一个XQM的文件。

1、首先,编写触发器的xqm文件,比如我的保存路径为/db/Triggers/TriggerTest01.xqm
TriggerTest01.xqm

xquery version "3.0";

module namespace trigger="http://exist-db.org/xquery/trigger";

declare namespace xmldb="http://exist-db.org/xquery/xmldb";

declare function trigger:before-create-collection($uri as xs:anyURI)
{
    local:log-event("before", "create", "collection", $uri)
};

declare function trigger:after-create-collection($uri as xs:anyURI)
{
    local:log-event("after", "create", "collection", $uri)
};

declare function trigger:before-copy-collection($uri as xs:anyURI, $new-uri as xs:anyURI)
{
    local:log-event("before", "copy", "collection", concat("from: ", $uri, " to:", $new-uri))
};

declare function trigger:after-copy-collection($new-uri as xs:anyURI, $uri as xs:anyURI)
{
    local:log-event("after", "copy", "collection", concat("from: ", $uri, " to:", $new-uri))
};

declare function trigger:before-move-collection($uri as xs:anyURI, $new-uri as xs:anyURI)
{
    local:log-event("before", "move", "collection", concat("from: ", $uri, " to:", $new-uri))
};

declare function trigger:after-move-collection($new-uri as xs:anyURI, $uri as xs:anyURI)
{
    local:log-event("after", "move", "collection", concat("from: ", $uri, " to:", $new-uri))
};

declare function trigger:before-delete-collection($uri as xs:anyURI)
{
    local:log-event("before", "delete", "collection", $uri)
};

declare function trigger:after-delete-collection($uri as xs:anyURI)
{
    local:log-event("after", "delete", "collection", $uri)
};

declare function trigger:before-create-document($uri as xs:anyURI)
{
    local:log-event("before", "create", "document", $uri)
};

declare function trigger:after-create-document($uri as xs:anyURI)
{
    local:log-event("after", "create", "document", $uri)
};

declare function trigger:before-update-document($uri as xs:anyURI)
{
    local:log-event("before", "update", "document", $uri)
};

declare function trigger:after-update-document($uri as xs:anyURI)
{
    local:log-event("after", "update", "document", $uri)
};

declare function trigger:before-copy-document($uri as xs:anyURI, $new-uri as xs:anyURI)
{
    local:log-event("before", "copy", "document", concat("from: ", $uri, " to: ", $new-uri))
};

declare function trigger:after-copy-document($new-uri as xs:anyURI, $uri as xs:anyURI)
{
    local:log-event("after", "copy", "document", concat("from: ", $uri, " to: ", $new-uri))
};

declare function trigger:before-move-document($uri as xs:anyURI, $new-uri as xs:anyURI)
{
    local:log-event("before", "move", "document", concat("from: ", $uri, " to: ", $new-uri))
};

declare function trigger:after-move-document($new-uri as xs:anyURI, $uri as xs:anyURI)
{
    local:log-event("after", "move", "document", concat("from: ", $uri, " to: ", $new-uri))
};

declare function trigger:before-delete-document($uri as xs:anyURI)
{
    local:log-event("before", "delete", "document", $uri)
};

declare function trigger:after-delete-document($uri as xs:anyURI)
{
    local:log-event("after", "delete", "document", $uri)
};

declare function local:log-event($type as xs:string, $event as xs:string, $object-type as xs:string, $uri as xs:string)
{
    let $log-collection := "/db/Triggers"
    let $log := "log01.xml"
    let $log-uri := concat($log-collection, "/", $log)
    return
    (
        (: create the log file if it does not exist 🙂
        if (not(doc-available($log-uri))) then
            xmldb:store($log-collection, $log, <triggers/>)
        else ()
        ,
        (: log the trigger details to the log file 🙂
        update insert <trigger event="{string-join(($type, $event, $object-type), '-')}" uri="{$uri}" timestamp="{current-dateTime()}"/> into doc($log-uri)/triggers
    )
};

Continue reading eXistDB简单Tirgger示例01

Redis与Tomcat集群集成

在网站访问量急剧上升时,通常需要使用集群的方法进行横向扩展。
对于没有状态的应用来说,直接用nginx进行处理即可。
但对于有状态的应用来说,比如登录状态等,除了使用nginx进行扩展外,就需要考虑到Session共享的问题了。

大家知道可以用apache+tomcat来实现Session共享,但效率太低了,而且容易出错。
今天说的主要是用nginx+tomcat+redis+tomcat-redis-session-manager的方式实现共享。
原理比较简单:

1、tomcat-redis-session-manage扩展了
org.apache.catalina.valves.ValveBase;
org.apache.catalina.session.ManagerBase;
org.apache.catalina.session.StandardSession;
并通过Tomcat配置,替代了这几个类。

2、Set属性时,用session id作为key,将Tomcat的整个Session拆分为SessionSerializationMetadata+RedisSession然后序列化为byte[],存放到Redis。

3、Get属性时,用session id作为key,从Redis获取byte[],然后反序列化为SessionSerializationMetadata+RedisSession,供Tomcat使用。

配置也很简单:
1、从github下载源码tomcat-redis-session-manager

2、用gradle进行编译

#master分支下面,要把signing段和uploadArchives段删掉,才能正常编译
#release就不需要了
gradle build

3、将三个Jar包拷贝到Tomcat的lib文件夹下

#%TOMCAT_HOME%/lib
tomcat-redis-session-manager-master-2.0.0.jar
commons-pool2-2.2.jar
jedis-2.5.2.jar

4、修改context.xml配置文件,新增下面内容就搞定咯

<!--%TOMCAT_HOME%/conf/context.xml-->
  <Valve className="com.orangefunction.tomcat.redissessions.RedisSessionHandlerValve" />
  <Manager className="com.orangefunction.tomcat.redissessions.RedisSessionManager" 
  host="localhost" port="6379" database="0" maxInactiveInterval="60"/>

好处是:不需要修改应用
坏处是:要耗费一定的时间来(序列化+保存到Redis)、(反序列化+从Redis读取)。

eXistDB简单通讯12(HTTP_SOAP)

  • 保存文件
  • 取回文件
  • 查询

1、QueryFileSOAP.java

package com.neohope.existdb.test;

import org.exist.soap.Query;
import org.exist.soap.QueryResponse;
import org.exist.soap.QueryService;
import org.exist.soap.QueryServiceLocator;

import java.net.URL;
import java.nio.charset.Charset;

public class QueryFileSOAP {
    public static void QueryXML(String xquery, String user, String pwd) throws Exception {
        QueryService service = new QueryServiceLocator();
        Query query = service.getQuery(new URL("http://localhost:8080/exist/services/Query"));
        String sessionId = query.connect("neotest", "neotest");

        byte[] queryData = xquery.getBytes(Charset.forName("UTF-8"));
        QueryResponse resp = query.xquery( sessionId, queryData );
        System.out.println( "found: " + resp.getHits() );
        if(resp.getHits() == 0) {
            return;
        }
        else {
            //get 10 results
            byte[][] hits = query.retrieveData(sessionId, 1, 10,
                    true, false, "elements").getElements();
            for (int i = 0; i < hits.length; i++) {
                System.out.println(new String(hits[i], "UTF-8"));
            }
        }

        query.disconnect(sessionId);
    }

    public static void main(String args[]) throws Exception {
        String user = "neotest";
        String pwd = "neotest";
        String query ="for $name in collection('/db/CDA')/ClinicalDocument/recordTarget/patientRole/patient/name \n" +
                "return \n" +
                "<name>{$name}</name> ";
        QueryXML(query, user, pwd);
    }
}

eXistDB简单通讯11(HTTP_SOAP)

  • 保存文件
  • 取回文件
  • 查询

1、GetFileSOAP.java

package com.neohope.existdb.test;

import org.exist.soap.Query;
import org.exist.soap.QueryService;
import org.exist.soap.QueryServiceLocator;

import java.net.URL;


public class GetFileSOAP {
    public static void GetXML(String fileId, String user, String pwd) throws Exception {
        QueryService service = new QueryServiceLocator();
        Query query = service.getQuery(new URL("http://localhost:8080/exist/services/Query"));
        String session = query.connect(user, pwd);

        byte[] data = query.getResourceData(session,
                "/db/CDA/"+fileId,
                true, false, false);
        System.out.println(new String(data, "UTF-8"));
        query.disconnect(session);
    }

    public static void main(String args[]) throws Exception {
        String user = "neotest";
        String pwd = "neotest";
        GetXML("入院患者护理评估单01.xml",user,pwd);
    }
}

eXistDB简单通讯10(HTTP_SOAP)

  • 保存文件
  • 取回文件
  • 查询

1、SaveFileSOAP.java

package com.neohope.existdb.test;

import org.exist.soap.*;

import java.io.BufferedReader;
import java.io.FileReader;
import java.net.URL;
import java.nio.charset.Charset;

public class SaveFileSOAP {
    public static void SaveXML(String xmlFilePath, String user, String pwd) throws Exception {
        AdminService adminService = new AdminServiceLocator();
        Admin admin = adminService.getAdmin(new URL("http://localhost:8080/exist/services/Admin"));
        String session = admin.connect("neotest", "neotest");

        BufferedReader f = new BufferedReader(new FileReader(xmlFilePath));
        String line;
        StringBuffer xml = new StringBuffer();
        while ((line = f.readLine()) != null)
            xml.append(line);
        f.close();

        admin.store(session, xml.toString().getBytes(Charset.forName("UTF-8")), "UTF-8", "/db/CDA/入院患者护理评估单02.xml", true);
        admin.disconnect(session);
    }

    public static void main( String[] args ) throws Exception {
        String user = "neotest";
        String pwd = "neotest";
        SaveXML("PATH_TO_FILE\\入院患者护理评估单02.xml", user, pwd);
    }
}