xttprep.tmpl

简介: SET FEEDBACK OFF NUMWIDTH 10 LINESIZE 32767 TRIMSPOOL ON TAB OFF PAGESIZE 0 EMB ONSET APPINFO ON DEFINE "&" VERIFY OFF SERVEROUTPUT ON SIZE 1000000 F...

SET FEEDBACK OFF NUMWIDTH 10 LINESIZE 32767 TRIMSPOOL ON TAB OFF PAGESIZE 0 EMB ON
SET APPINFO ON DEFINE "&" VERIFY OFF SERVEROUTPUT ON SIZE 1000000 FORMAT TRUNCATED

-- ALTER SESSION SET PLSQL_CCFLAGS='XTT_TESTING:TRUE';

-- REM DON'T FORGET ABOUT THIS SPOOL
SPOOL %%tmp%%/xttprepare.cmd
DECLARE
l_detnew CONSTANT BOOLEAN := ('%%type%%' = 'DETNEW');
l_prepare CONSTANT BOOLEAN := ('%%type%%' = 'PREPARE');
l_transfer CONSTANT BOOLEAN := ('%%type%%' = 'TRANSFER');
l_prepnext CONSTANT BOOLEAN := ('%%type%%' = 'PREPNEXT');
l_transport CONSTANT BOOLEAN := ('%%type%%' = 'BACKUP');
lc_batch_size CONSTANT NUMBER := 10000;
DEBUG CONSTANT BOOLEAN := FALSE;
CRLF CONSTANT BOOLEAN := TRUE;
PLAN CONSTANT BOOLEAN := TRUE;
l_tsn dbms_sql.number_table;
l_names dbms_sql.varchar2_table;
l_dnames dbms_sql.varchar2_table;
l_fnames dbms_sql.varchar2_table;
l_prev user_tablespaces.tablespace_name%TYPE;
l_files dbms_sql.number_table;
l_ckpch dbms_sql.number_table;
l_pname v$database.platform_name%TYPE;
l_stageondest CONSTANT VARCHAR2(4000) := '%%stageondest%%';
l_storageondest CONSTANT VARCHAR2(4000) := '%%storageondest%%';
l_dfcopydir CONSTANT VARCHAR2(4000) := '%%dfcopydir%%';
l_tmp CONSTANT VARCHAR2(4000) := '%%tmp%%';
l_parallelism CONSTANT VARCHAR2(200) := '%%parallel%%';
l_backupdir CONSTANT VARCHAR2(4000) := '%%backupformat%%';

-- The hints for the query below are handcrafted
-- and should cover 11g (w/ and w/o the fix for
-- bug#8248459 in place) well as 12c. Ideally a
-- backport of 8248459 has to be applied on both
-- source and target databases.
CURSOR dc
IS

SELECT ts#
     , name
     , df.dname
     , df.fname
     , file#
     , checkpoint_change#
  FROM (
       SELECT /*+
                LEADING(t.x$kccts)
                USE_HASH(d.df)
                FULL(t.x$kccts)
                FULL(d.df)
                USE_HASH(d.fe)
                USE_HASH(d.fn)
                USE_HASH(d.fh)
                LEADING(d.fe d.fn d.fh)
              */
              ROW_NUMBER()
              OVER (
                PARTITION BY d.ts# ORDER BY file#
              ) rn
            , MIN(
                CASE
                  WHEN enabled = 'READ WRITE'
                   AND status = 'ONLINE'
                  THEN d.ts#
                  ELSE -d.ts#
                END
              ) OVER (
                 PARTITION BY d.ts#
              ) ts#
            , t.name
            , REGEXP_REPLACE(d.name, '(.*)/(.*)', '\1') dname
            , REGEXP_REPLACE(d.name, '(.*)/(.*)', '\2') fname
            , file#
            , MIN(checkpoint_change#)
              OVER (
                PARTITION BY d.ts#
              ) checkpoint_change#
         FROM $IF $$XTT_TESTING
              $THEN
              (
              SELECT USERENV('INSTANCE') inst_id
                   , ts#
                   , CASE WHEN ts# = 0
                          THEN status
                          WHEN ts# IN (8)
                          THEN 'OFFLINE'
                          ELSE 'ONLINE'
                     END status
                   , CASE WHEN ts# = 0
                          THEN enabled
                          WHEN ts# IN (9)
                          THEN 'READ ONLY'
                          ELSE 'READ WRITE'
                     END enabled
                   , file#
                   , checkpoint_change#
                FROM gv$datafile
              )
              $ELSE
              gv$datafile
              $END d
            , v$tablespace t
        WHERE d.ts# = t.ts#
          AND d.inst_id = USERENV('INSTANCE')
          AND t.name IN (
                %%TABLESPACES%%
              )
      ) df
 WHERE rn = 1
    OR ts# > 0
 ORDER BY
       ts#
     , rn
;

PROCEDURE d (

i_msg           VARCHAR2 DEFAULT NULL

, i_crlf BOOLEAN DEFAULT TRUE
)
IS
BEGIN

IF (DEBUG)
THEN
  IF (i_crlf)
  THEN
    dbms_output.put_line(
      '#DEBUG:' || i_msg
    );
  ELSE
    dbms_output.put(
      '#DEBUG:' || i_msg
    );
  END IF;
END IF;

END d;

PROCEDURE t (

i_msg           VARCHAR2 DEFAULT NULL

, i_crlf BOOLEAN DEFAULT TRUE
)
IS

l_prepend       VARCHAR2(256) := '#TRANSFER:';

BEGIN

IF (l_transfer)
THEN
  IF (i_crlf)
  THEN
    dbms_output.put_line(
      l_prepend || 'source_file_name=' || i_msg || ''
    );
  ELSE
    dbms_output.put(
      l_prepend || 'source_file_name=' || i_msg || ''
    );
  END IF;
END IF;

END t;

PROCEDURE p (

i_msg           VARCHAR2 DEFAULT NULL

, i_crlf BOOLEAN DEFAULT TRUE
)
IS

l_prepend       VARCHAR2(256) := CASE
                                   WHEN (l_prepare OR l_transfer OR 
                                         l_transport)
                                     THEN '#PLAN:'
                                     ELSE ' '
                                 END;

BEGIN

IF ((l_prepare OR l_transfer OR l_transport) OR l_detnew)
THEN
  IF (i_crlf)
  THEN
    dbms_output.put_line(
      l_prepend || i_msg
    );
  ELSE
    dbms_output.put(
      l_prepend || i_msg
    );
  END IF;
END IF;

END p;

PROCEDURE r (

i_msg           VARCHAR2 DEFAULT NULL

, i_crlf BOOLEAN DEFAULT TRUE
)
IS
BEGIN

IF (l_prepare) THEN
  IF (i_crlf)
  THEN
    dbms_output.put_line(
      '#CONVERT:' || i_msg
    );
  ELSE
    dbms_output.put(
      '#CONVERT:' || i_msg
    );
  END IF;
END IF;

END r;

PROCEDURE cp (

i_msg           VARCHAR2 DEFAULT NULL

, i_crlf BOOLEAN DEFAULT TRUE
)
IS
BEGIN

IF (l_prepare OR l_transport)
THEN
  IF (i_crlf)
  THEN
    dbms_output.put_line(i_msg);
  ELSE
    dbms_output.put(i_msg);
  END IF;
END IF;

END cp;

PROCEDURE t_listdatfiles (

i_msg           VARCHAR2 DEFAULT NULL

, i_dfdir BOOLEAN DEFAULT FALSE
)
IS

l_prepend       VARCHAR2(256) := '#FNAME:';

BEGIN


IF (l_transfer)
THEN
IF (i_dfdir)
THEN
  l_prepend := '#DNAME:';
END IF;

dbms_output.put_line(
    l_prepend || i_msg
    );
END IF;

END t_listdatfiles;

PROCEDURE t_newdatafiles (

i_msg           VARCHAR2 DEFAULT NULL

, i_crlf BOOLEAN DEFAULT TRUE
)
IS

l_prepend       VARCHAR2(256) := '#NEWDESTDF:';

BEGIN

IF (l_transfer OR l_transport)
THEN
  IF (i_crlf)
  THEN
    dbms_output.put_line(
      l_prepend || i_msg
    );
  ELSE
    dbms_output.put(
      l_prepend || i_msg
    );
  END IF;
END IF;

END t_newdatafiles;

BEGIN
OPEN dc;
LOOP

FETCH dc
 BULK COLLECT
 INTO l_tsn
    , l_names
    , l_dnames  
    , l_fnames  
    , l_files
    , l_ckpch
LIMIT lc_batch_size;
EXIT WHEN l_tsn.COUNT = 0;
FOR i IN 1..l_tsn.COUNT
LOOP
  IF (((l_prepare OR l_transfer OR l_transport) OR l_prepnext)  AND 
      l_tsn(i) < 1)
  THEN
    d( 'Tablespace ' || l_names(i) || ' [' || -l_tsn(i) || ']'
    || ' is special, read only or has some offline files! Skipping...'
    );
    RAISE_APPLICATION_ERROR(-20001, 'TABLESPACE(S) IS READONLY OR,
                                     OFFLINE JUST CONVERT, COPY');
  ELSE
    IF (l_prepare AND l_pname IS NULL)
    THEN
      SELECT platform_name
        INTO l_pname
        FROM v$database;
    END IF;
    t_listdatfiles(l_dnames(i), TRUE);
    t_listdatfiles(l_fnames(i));
    d( 'Processing file# ' || l_files(i)
    || ' with checkpoint_change# ' || l_ckpch(i) || ' of '
    || l_names(i) || ' [' || l_tsn(i) || ']'
    );
    -- June 04 2014: Following change was done to allow many datafiles
    -- to be copied together instead of be done in a serial manner.
    -- It will be like backup as copy datafile x,y,z instead of the
    -- current backup as copy datafile x, backup as copy datafile y.
    IF (l_prev IS NULL)
    THEN
      p(l_names(i) || '::::' || TO_CHAR(l_ckpch(i), 'FM999999999999999'));
      r('host ''echo ts::' || l_names(i) || ''';');
      IF (l_prepare) THEN
        r('  convert from platform ''' || l_pname || '''');
        r('  datafile ');
        cp('backup as copy tag ''' ||  'prepare' || ''' datafile');
      END IF;
      IF (l_transport) THEN
        cp('backup for transport allow inconsistent ' ||
           'incremental level 0 datafile');
      END IF;
    END IF;
    -- June 04 2014: Following change was done to allow many datafiles
    -- to be copied together instead of be done in a serial manner.
    -- It will be like backup as copy datafile x,y,z instead of the
    -- current backup as copy datafile x, backup as copy datafile y.
    IF (l_prev <> l_names(i))
    THEN
       r('  format ''' || l_storageondest || '/%N_%f.xtf''');
       r(' parallelism ' || l_parallelism || ';');
       p(l_names(i) || '::::' || TO_CHAR(l_ckpch(i), 'FM999999999999999'));
       r('host ''echo ts::' || l_names(i) || ''';');
       r('  convert from platform ''' || l_pname || '''');
       r('  datafile ');
    END IF; 
    r('  ' ||
      CASE
        WHEN l_prev = l_names(i)
        THEN ','
        ELSE ' '
      END 
    || ''''
    || l_stageondest || '/' || l_names(i)
    || '_' || l_files(i) || '.tf'''
    );
    -- Feb 2015: Print the directory names also
    t( 
       l_names(i) || ',' || l_dnames(i) || ',' || l_fnames(i)
    );
    t_newdatafiles( 
      CASE
    WHEN l_transport THEN
      l_files(i) || ',' || l_storageondest || '/'
      || l_fnames(i)
    ELSE
      -- Feb 2015: Print the directory names also
      l_files(i) || ',' || 'DESTDIR:' || l_dnames(i) || ',' || '/'
      || l_fnames(i)
    END
    );
    cp(
   CASE
       -- June 04 2014: Following change was done to allow many datafiles
       -- to be copied together instead of be done in a serial manner.
       -- It will be like backup as copy datafile x,y,z instead of the
       -- current backup as copy datafile x, backup as copy datafile y.
        WHEN l_prev IS NULL
        THEN ' '
        ELSE ','
   END
    || l_files(i)
    );
    p(l_files(i));
    l_prev := l_names(i);
  END IF;
END LOOP;

END LOOP;
IF (l_prepare AND l_prev IS NOT NULL)
THEN

r('  format ''' || l_storageondest || '/%N_%f.xtf''');
r(' parallelism ' || l_parallelism || ';');
cp('  format ''' || l_dfcopydir || '/%N_%f.tf'';');

END IF;
IF (l_transport AND l_prev IS NOT NULL)
THEN

cp('  format ''' || l_backupdir || '/%N_%f_%U.bkp'';');

END IF;
CLOSE dc;
END;
/
SPOOL OFF
EXIT

目录
相关文章
kde
|
5天前
|
JSON Linux 数据格式
Docker镜像加速指南:手把手教你配置国内镜像源
配置国内镜像源可大幅提升 Docker 拉取速度,解决访问 Docker Hub 缓慢问题。本文详解 Linux、Docker Desktop 配置方法,并提供测速对比与常见问题解答,附最新可用镜像源列表,助力高效开发部署。
kde
3121 8
|
5天前
|
JavaScript Ubuntu IDE
国内如何安装和使用 Claude Code镜像教程 - Windows 用户篇
国内如何安装和使用 Claude Code镜像教程 - Windows 用户篇
569 0
|
8天前
|
人工智能 定位技术 API
Dify MCP 保姆级教程来了!
大语言模型,例如 DeepSeek,如果不能联网、不能操作外部工具,只能是聊天机器人。除了聊天没什么可做的。
838 9
|
14天前
|
Java Linux Maven
2025年最新版最细致Maven安装与配置指南(任何版本都可以依据本文章配置)
本文详细介绍了Maven的项目管理工具特性、安装步骤和配置方法。主要内容包括: Maven概述:解释Maven作为基于POM的构建工具,具备依赖管理、构建生命周期和仓库管理等功能。 安装步骤: 从官网下载最新版本 解压到指定目录 创建本地仓库文件夹 关键配置: 修改settings.xml文件 配置阿里云和清华大学镜像仓库以加速依赖下载 设置本地仓库路径 附加说明:包含详细的配置示例和截图指导,适用于各种操作系统环境。 本文提供了完整的Maven安装和配置
2025年最新版最细致Maven安装与配置指南(任何版本都可以依据本文章配置)
|
3天前
|
人工智能 Java Spring
【保姆级图文详解】大模型、Spring AI编程调用大模型
【保姆级图文详解】大模型、Spring AI编程调用大模型
356 7
【保姆级图文详解】大模型、Spring AI编程调用大模型
|
9天前
|
数据采集 JSON API
Excel数据治理新思路:引入智能体实现自动纠错【Python+Agent】
本文介绍如何利用智能体与Python代码批量处理Excel中的脏数据,解决人工录入导致的格式混乱、逻辑错误等问题。通过构建具备数据校验、异常标记及自动修正功能的系统,将数小时的人工核查任务缩短至分钟级,大幅提升数据一致性和办公效率。
|
8天前
|
存储 人工智能 自然语言处理
DeepSeek R1+Open WebUI实现本地知识库的搭建和局域网访问
本文介绍了使用 DeepSeek R1 和 Open WebUI 搭建本地知识库的详细步骤与注意事项,涵盖核心组件介绍、硬件与软件准备、模型部署、知识库构建及问答功能实现等内容,适用于本地文档存储、向量化与检索增强生成(RAG)场景的应用开发。
368 0
|
7天前
|
人工智能 大数据 开发者
让AI时代的卓越架构触手可及,阿里云技术解决方案开放免费试用
阿里云推出基于场景的解决方案免费试用活动,新老用户均可领取100点试用点,完成部署还可再领最高100点,相当于一年可获得最高200元云资源。覆盖AI、大数据、互联网应用开发等多个领域,支持热门场景如DeepSeek部署、模型微调等,助力企业和开发者快速验证方案并上云。
305 22
让AI时代的卓越架构触手可及,阿里云技术解决方案开放免费试用
|
8天前
|
编解码 物联网 开发者
FLUX.1 Kontext 的全生态教程来啦!AIGC专区在线试玩!
Flux.1 Kontext [dev] 开源模型大家都用上了吗?小编汇总了3个使用教程,打包送上!
426 1

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等