XTTS基本步骤

1. 源端对表空间做0级备份
2. 目标端提取恢复文件
3. 源端对表空间做1级增量备份
4. 目标端应用1级增量备份
5. 源端设置表空间只读—停机时间开始
6. 源端再做1级增量备份
7. 源端导出创建用户的脚本，导出表空间元数据
8. 目标端应用1级增量备份
9. 目标端创建用户及导入表空间元数据
10. 目标端修改用户默认表空间并设置表空间可读写

源端：测试表空间准备

Microsoft Windows [版本 6.1.7601]

版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

 

C:\Users\Administrator>sqlplus /nolog

 

SQL*Plus: Release 10.2.0.5.0 - Production on 星期日 1月 19 10:37:04 2020

 

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

 

SQL> conn /as sysdba

已连接。

SQL> select name from v$datafile;

 

NAME

--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS101.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS201.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS102.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS103.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS202.DBF

 

已选择9行。

 

SQL> create tablespace jyctbs datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\jyctbs01.dbf' size 10M;

 

 

表空间已创建。

 

SQL> alter tablespace jyctbs add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\jyctbs02.dbf' size 1

0M;

 

表空间已更改。

 

SQL> alter tablespace jyctbs add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\jyctbs03.dbf' size 1

0M;

 

表空间已更改。

 

SQL> create user jyc identified by jyc default tablespace jyctbs;

 

用户已创建。

 

SQL> grant dba to jyc;

 

授权成功。

 

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\jyctbs02.dbf' autoextend on;

 

数据库已更改。

 

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\jyctbs01.dbf' autoextend on;

 

数据库已更改。

 

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\jyctbs03.dbf' autoextend on;

 

数据库已更改。

 

SQL> conn jyc/jyc

已连接。

SQL> create table t1 as select object_name from dba_objects;

 

表已创建。

 

SQL> select count(*) from dba_objects;

 

  COUNT(*)

----------

     11000

 

SQL> set line 132

SQL> set wrap off

SQL> col name for a50

SQL> col tbsname for a10

SQL> select a.file#,a.ts#,b.name tbsname,a.name from v$datafile a,v$tablespace b where a.ts#=b.ts# o

rder by b.name asc,a.file# asc;

 

     FILE#        TS# TBSNAME    NAME

---------- ---------- ---------- --------------------------------------------------

        10          7 JYCTBS     C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS01.DBF

        11          7 JYCTBS     C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS02.DBF

        12          7 JYCTBS     C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS03.DBF

         5          5 PARTTBS1   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS101.DB

         7          5 PARTTBS1   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS102.DB

         8          5 PARTTBS1   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS103.DB

         6          6 PARTTBS2   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS201.DB

         9          6 PARTTBS2   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS202.DB

         3          2 SYSAUX     C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\SYSAUX01.DBF

         1          0 SYSTEM     C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\SYSTEM01.DBF

         2          1 UNDOTBS1   C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\UNDOTBS01.DBF

 

     FILE#        TS# TBSNAME    NAME

---------- ---------- ---------- --------------------------------------------------

         4          4 USERS      C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\USERS01.DBF

 

已选择12行。

 

SQL>

C:\Users\Administrator>mount -u:root -p:"password" 192.168.52.155:/backup E:

E: 现已成功连接到 192.168.52.155:/backup

 

命令已成功完成。

源端：0级备份

SQL> alter database enable block change tracking using file 'C:\soft\change.log';

SQL> select status, filename from v$block_change_tracking;

 

STATUS     FILENAME

---------- -------------------------------------------------

ENABLED    C:\SOFT\CHANGE.LOG

 

C:\Users\Administrator>rman target /

 

恢复管理器: Release 10.2.0.5.0 - Production on 星期日 1月 19 10:45:35 2020

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

连接到目标数据库: ERP (DBID=278480429)

 

RMAN> show all ;

 

使用目标数据库控制文件替代恢复目录

RMAN 配置参数为:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFERP.ORA'; # default

 

 

RMAN> backup as compressed backupset incremental        level=0 tablespace      jyctbs  format '\\192.168.52.155\backup\jyctbs_%U' tag=jyctbs0;

 

启动 backup 于 19-1月 -20

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=204 devtype=DISK

分配的通道: ORA_DISK_2

通道 ORA_DISK_2: sid=192 devtype=DISK

通道 ORA_DISK_1: 启动压缩的增量级别 0 数据文件备份集

通道 ORA_DISK_1: 正在指定备份集中的数据文件

输入数据文件 fno=00010 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS01.DBF

输入数据文件 fno=00012 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS03.DBF

通道 ORA_DISK_1: 正在启动段 1 于 19-1月 -20

通道 ORA_DISK_2: 启动压缩的增量级别 0 数据文件备份集

通道 ORA_DISK_2: 正在指定备份集中的数据文件

输入数据文件 fno=00011 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS02.DBF

通道 ORA_DISK_2: 正在启动段 1 于 19-1月 -20

通道 ORA_DISK_1: 已完成段 1 于 19-1月 -20

段句柄=\\192.168.52.155\BACKUP\JYCTBS_1EUMC5AQ_1_1 标记=JYCTBS0 注释=NONE

通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:07

通道 ORA_DISK_2: 已完成段 1 于 19-1月 -20

段句柄=\\192.168.52.155\BACKUP\JYCTBS_1FUMC5AQ_1_1 标记=JYCTBS0 注释=NONE

通道 ORA_DISK_2: 备份集已完成, 经过时间:00:00:07

完成 backup 于 19-1月 -20

 

RMAN>

RMAN> list backup tag=jyctbs0;

 

 

备份集列表

===================

 

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间

------- ---- -- ---------- ----------- ------------ ----------

24      Incr 0  96.00K     DISK        00:00:07     19-1月 -20

        BP 关键字: 24   状态: AVAILABLE  已压缩: YES  标记: JYCTBS0

段名:\\192.168.52.155\BACKUP\JYCTBS_1FUMC5AQ_1_1

  备份集 24 中的数据文件列表

  文件 LV 类型 Ckp SCN    Ckp 时间   名称

  ---- -- ---- ---------- ---------- ----

  11   0  Incr 558062     19-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS02.DBF

 

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间

------- ---- -- ---------- ----------- ------------ ----------

25      Incr 0  496.00K    DISK        00:00:07     19-1月 -20

        BP 关键字: 25   状态: AVAILABLE  已压缩: YES  标记: JYCTBS0

段名:\\192.168.52.155\BACKUP\JYCTBS_1EUMC5AQ_1_1

  备份集 25 中的数据文件列表

  文件 LV 类型 Ckp SCN    Ckp 时间   名称

  ---- -- ---- ---------- ---------- ----

  10   0  Incr 558064     19-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS01.DBF

  12   0  Incr 558064     19-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS03.DBF

 

目标端：提取0级备份：

startup nomount

set serveroutput on;

DECLARE

devtype varchar2(256);

done Boolean;

BEGIN

Devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.restoreSetDatafile;

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'/home/db/oracle/oradata/erp/JYCTBS01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>12,toname=>'/home/db/oracle/oradata/erp/JYCTBS03.dbf');

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/backup/JYCTBS_1EUMC5AQ_1_1', params=>null);

sys.dbms_backup_restore.deviceDeallocate;END;

/

 

 

set serveroutput on;

DECLARE

devtype varchar2(256);

done Boolean;

BEGIN

Devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

sys.dbms_backup_restore.restoreSetDatafile;

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>11,toname=>'/home/db/oracle/oradata/erp/JYCTBS02.dbf');

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/backup/JYCTBS_1FUMC5AQ_1_1', params=>null);

sys.dbms_backup_restore.deviceDeallocate;END;

/

源端：增加测试数据

C:\Users\Administrator>sqlplus jyc/jyc

 

SQL*Plus: Release 10.2.0.5.0 - Production on 星期日 1月 19 11:12:50 2020

 

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

 

 

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

SQL> desc t1;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 OBJECT_NAME                                        VARCHAR2(128)

 

SQL> insert into t1 values('123');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select count(*) from t1;

 

  COUNT(*)

----------

     11001

 

源端：做一次1级增量备份

C:\Users\Administrator>rman target /

 

恢复管理器: Release 10.2.0.5.0 - Production on 星期日 1月 19 11:14:30 2020

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

连接到目标数据库: ERP (DBID=278480429)

 

RMAN> run {

allocate channel t1 type disk;

backup       incremental     level=1 tablespace      jyctbs  format '\\192.168.52.155\backup\jyctbs_%U' tag=jyctbs1;

release channel t1;

}

 

使用目标数据库控制文件替代恢复目录

分配的通道: t1

通道 t1: sid=189 devtype=DISK

 

启动 backup 于 19-1月 -20

通道 t1: 启动增量级别 1 数据文件备份集

通道 t1: 正在指定备份集中的数据文件

输入数据文件 fno=00010 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS01.DBF

输入数据文件 fno=00011 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS02.DBF

输入数据文件 fno=00012 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS03.DBF

通道 t1: 正在启动段 1 于 19-1月 -20

通道 t1: 已完成段 1 于 19-1月 -20

段句柄=\\192.168.52.155\BACKUP\JYCTBS_1GUMC691_1_1 标记=JYCTBS1 注释=NONE

通道 t1: 备份集已完成, 经过时间:00:00:07

完成 backup 于 19-1月 -20

 

释放的通道: t1

目标端：应用1级增量备份

SQL> !ls -l /home/db/oracle/oradata/erp/JYCTBS*

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:09 /home/db/oracle/oradata/erp/JYCTBS01.dbf

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:09 /home/db/oracle/oradata/erp/JYCTBS02.dbf

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:09 /home/db/oracle/oradata/erp/JYCTBS03.dbf

 

SQL>

SQL>

SQL>

SQL> set serveroutput on;

SQL> DECLARE

    outhandle varchar2(512) ;

    outtag varchar2(30) ;

    done boolean ;

    failover boolean ;

    devtype VARCHAR2(512);

    BEGIN

    DBMS_OUTPUT.put_line('Entering RollForward');

    -- Now the rolling forward.

   devtype := sys.dbms_backup_restore.deviceAllocate;

   sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE) ;

   DBMS_OUTPUT.put_line('After applySetDataFile');

   sys.dbms_backup_restore.applyDatafileTo(dfnumber   =>      10      ,toname => '/home/db/oracle/oradata/erp/JYCTBS01.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);

  DBMS_OUTPUT.put_line('Done: applyDataFileTo');

  sys.dbms_backup_restore.applyDatafileTo(dfnumber   =>      11      ,toname => '/home/db/oracle/oradata/erp/JYCTBS02.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);

   DBMS_OUTPUT.put_line('Done: applyDataFileTo');

  sys.dbms_backup_restore.applyDatafileTo(dfnumber   =>      12      ,toname => '/home/db/oracle/oradata/erp/JYCTBS03.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);

   DBMS_OUTPUT.put_line('Done: applyDataFileTo');

  -- Restore Set Piece

   sys.dbms_backup_restore.restoreSetPiece(handle     => '/backup/JYCTBS_1GUMC691_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ;

   DBMS_OUTPUT.put_line('Done: RestoreSetPiece');

   -- Restore Backup Piece

   sys.dbms_backup_restore.restoreBackupPiece(done =>  done, params =>  null, outhandle  => outhandle,outtag => outtag, failover => failover);

   DBMS_OUTPUT.put_line('Done: RestoreBackupPiece');

   sys.dbms_backup_restore.restoreCancel(TRUE);

   sys.dbms_backup_restore.deviceDeallocate;

   END;

   /

Entering RollForward

After applySetDataFile

Done: applyDataFileTo

Done: applyDataFileTo

Done: applyDataFileTo

Done: RestoreSetPiece

Done: RestoreBackupPiece

 

PL/SQL procedure successfully completed.

 

SQL> !ls -l /home/db/oracle/oradata/erp/JYCTBS*

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:20 /home/db/oracle/oradata/erp/JYCTBS01.dbf

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:20 /home/db/oracle/oradata/erp/JYCTBS02.dbf

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:20 /home/db/oracle/oradata/erp/JYCTBS03.dbf

 

SQL>

源端：再插入记录

SQL> select table_name from user_tables;

 

TABLE_NAME

------------------------------

T1

 

SQL> create table t2 as select * from user_objects;

 

表已创建。

 

SQL> select table_name from user_tables;

 

TABLE_NAME

------------------------------

T1

T2

 

SQL> select count(*) from t1;

 

  COUNT(*)

----------

     11001

 

SQL> select count(*) from t2;

 

  COUNT(*)

----------

         2

 

 

SQL> insert into t1 values ('kd');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select count(*) from t1;

 

  COUNT(*)

----------

     11002

 

SQL>

 

源端：设置只读表空间

 

SQL> conn /as sysdba

已连接。

SQL> alter tablespace jyctbs read only;

 

表空间已更改。

源端：最后做1级增量备份：

RMAN> run {

allocate channel t1 type disk;

backup       incremental     level=1 tablespace      jyctbs  format '\\192.168.52.155\backup\jyctbs_%U' tag=jyctbs1;

release channel t1;

}

 

分配的通道: t1

通道 t1: sid=189 devtype=DISK

 

启动 backup 于 19-1月 -20

通道 t1: 启动增量级别 1 数据文件备份集

通道 t1: 正在指定备份集中的数据文件

输入数据文件 fno=00010 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS01.DBF

输入数据文件 fno=00011 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS02.DBF

输入数据文件 fno=00012 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\JYCTBS03.DBF

通道 t1: 正在启动段 1 于 19-1月 -20

通道 t1: 已完成段 1 于 19-1月 -20

段句柄=\\192.168.52.155\BACKUP\JYCTBS_1HUMC8E2_1_1 标记=JYCTBS1 注释=NONE

通道 t1: 备份集已完成, 经过时间:00:00:07

完成 backup 于 19-1月 -20

 

释放的通道: t1

源端：导出表空间元数据

C:\Users\Administrator>exp "'sys/oracle as sysdba'" transport_tablespace=y tablespaces=('jyctbs') STATISTICS=none file=\

\192.168.52.155\backup\jyctbs.dmp log=\\192.168.52.155\backup\jyctbs.log

 

Export: Release 10.2.0.5.0 - Production on 星期日 1月 19 11:53:06 2020

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

 

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

注: 将不导出表数据 (行)

即将导出可传输的表空间元数据...

对于表空间 JYCTBS...

. 正在导出簇定义

. 正在导出表定义

. . 正在导出表                              T1

. . 正在导出表                              T2

. 正在导出引用完整性约束条件

. 正在导出触发器

. 结束导出可传输的表空间元数据

成功终止导出, 没有出现警告。

 

目标端：应用1级增量备份

SQL> set serveroutput on;

SQL> DECLARE

   outhandle varchar2(512) ;

   outtag varchar2(30) ;

   done boolean ;

   failover boolean ;

   devtype VARCHAR2(512);

   BEGIN

   DBMS_OUTPUT.put_line('Entering RollForward');

   -- Now the rolling forward.

   devtype := sys.dbms_backup_restore.deviceAllocate;

   sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE) ;

   DBMS_OUTPUT.put_line('After applySetDataFile');

   sys.dbms_backup_restore.applyDatafileTo(dfnumber   =>      10      ,toname => '/home/db/oracle/oradata/erp/JYCTBS01.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);

   DBMS_OUTPUT.put_line('Done: applyDataFileTo');

   sys.dbms_backup_restore.applyDatafileTo(dfnumber   =>      11      ,toname => '/home/db/oracle/oradata/erp/JYCTBS02.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);

   DBMS_OUTPUT.put_line('Done: applyDataFileTo');

   sys.dbms_backup_restore.applyDatafileTo(dfnumber   =>      12      ,toname => '/home/db/oracle/oradata/erp/JYCTBS03.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);

   DBMS_OUTPUT.put_line('Done: applyDataFileTo');

   -- Restore Set Piece

   sys.dbms_backup_restore.restoreSetPiece(handle     => '/backup/JYCTBS_1HUMC8E2_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ;

   DBMS_OUTPUT.put_line('Done: RestoreSetPiece');

   -- Restore Backup Piece

   sys.dbms_backup_restore.restoreBackupPiece(done =>  done, params =>  null, outhandle  => outhandle,outtag => outtag, failover => failover);

   DBMS_OUTPUT.put_line('Done: RestoreBackupPiece');

   sys.dbms_backup_restore.restoreCancel(TRUE);

   sys.dbms_backup_restore.deviceDeallocate;

   END;

   /

Entering RollForward

After applySetDataFile

Done: applyDataFileTo

Done: applyDataFileTo

Done: applyDataFileTo

Done: RestoreSetPiece

Done: RestoreBackupPiece

 

PL/SQL procedure successfully completed.

 

SQL> !ls -l /home/db/oracle/oradata/erp/JYCTBS*

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:54 /home/db/oracle/oradata/erp/JYCTBS01.dbf

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:54 /home/db/oracle/oradata/erp/JYCTBS02.dbf

-rw-r----- 1 oracle oinstall 10493952 Jan 19 11:54 /home/db/oracle/oradata/erp/JYCTBS03.dbf

源端：提取创建用户信息

set serveroutput on

set long 9999999

DECLARE

    --多个用户使用 ; 隔开

    v_users VARCHAR2(1000) := UPPER('JYC');

    v_sqls VARCHAR2(32767);

BEGIN

    dbms_output.enable(9999999);

    FOR x IN (SELECT regexp_substr(v_users, '[^;]+', 1, LEVEL) u

                FROM dual

              CONNECT BY LEVEL <= LENGTH(v_users) - LENGTH(REPLACE(v_users, ';')) + 1

             ) LOOP

         dbms_output.put_line(CHR(10));

         dbms_output.put_line('----------------------华丽丽地分割线----------------------------------');

         dbms_output.put_line('--用户'||x.u||'脚本');

 

         dbms_output.put_line('--创建用户');

         SELECT to_char(dbms_metadata.get_ddl('USER',x.u)) INTO v_sqls FROM dual;

         dbms_output.put_line(v_sqls||';');

         dbms_output.put_line('--系统权限');

         SELECT to_char(dbms_metadata.get_granted_ddl('SYSTEM_GRANT',x.u)) INTO v_sqls from dual;

         dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';'||CHR(10))||';', ';'));

         dbms_output.put_line('--角色权限');

         SELECT to_char(dbms_metadata.get_granted_ddl('ROLE_GRANT',x.u)) INTO v_sqls  from dual;

         dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';'||CHR(10))||';', ';'));

         dbms_output.put_line('--对象权限');

         SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT',x.u) INTO v_sqls from dual;

         dbms_output.put_line(ltrim(replace(v_sqls, CHR(10), ';'||CHR(10))||';', ';'));

    END LOOP;

    EXCEPTION

        WHEN OTHERS THEN

            dbms_output.put_line('Exception!');

END;

/

 

----------------------华丽丽地分割线----------------------------------

--用户JYC脚本

--创建用户

 

   CREATE USER "JYC" IDENTIFIED BY VALUES 'F5C8EB2C4827DF75'

      DEFAULT

TABLESPACE "JYCTBS"

      TEMPORARY TABLESPACE "TEMP"

 ;

--系统权限

 

  GRANT UNLIMITED TABLESPACE TO "JYC";

 ;

--角色权限

 

   GRANT "DBA" TO "JYC";

 ;

--对象权限

Exception!

 

PL/SQL 过程已成功完成。

 

目标端：创建用户

[oracle@saperp backup]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 11:58:01 2020

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

SQL> conn /as sysdba

Connected.

SQL> CREATE USER "JYC" IDENTIFIED BY jyc

  2        DEFAULT

  3  TABLESPACE "USERS"

  4        TEMPORARY TABLESPACE "TEMP"

  5   ;

 

User created.

 

SQL> GRANT UNLIMITED TABLESPACE TO "JYC";

 

Grant succeeded.

 

SQL>  GRANT "DBA" TO "JYC";

 

Grant succeeded.

 

SQL> exit

目标端：imp导入表空间元数据

[oracle@saperp backup]$ imp "'sys/oracle as sysdba'" transport_tablespace=y TABLESPACES='JYCTBS' file=jyctbs.dmp log=JYCTBS-imp.log datafiles='/home/db/oracle/oradata/erp/JYCTBS01.dbf','/home/db/oracle/oradata/erp/JYCTBS02.dbf','/home/db/oracle/oradata/erp/JYCTBS03.dbf'

 

Import: Release 11.2.0.4.0 - Production on Sun Jan 19 12:00:43 2020

 

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

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Export file created by EXPORT:V10.02.01 via conventional path

About to import transportable tablespace(s) metadata...

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SYS's objects into SYS

. importing SYS's objects into SYS

. importing JYC's objects into JYC

. . importing table                           "T1"

. . importing table                           "T2"

. importing SYS's objects into SYS

Import terminated successfully without warnings.

 

目标端：修改用户默认表空间

[oracle@saperp backup]$ sqlplus /nolog

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:00:50 2020

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

SQL> conn /as sysdba

Connected.

SQL>  alter user saperp default tablespace jyctbs;

 

User altered.

 

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

PARTTBS1                       ONLINE

USERS                          ONLINE

JYCTBS                         READ ONLY

 

7 rows selected.

 

SQL> conn jyc/jyc

Connected.

SQL> select count(*) from t1;

 

  COUNT(*)

----------

     11002

 

SQL> select count(*) from t2;

 

  COUNT(*)

----------

         2

 

SQL> set line 132

SQL> set wrap off

SQL> col name for a50

SQL> col tbsname for a10

SQL> select a.file#,a.ts#,b.name tbsname,a.name from v$datafile a,v$tablespace b where a.ts#=b.ts# order by b.name asc,a.file# asc;

 

     FILE#        TS# TBSNAME    NAME

---------- ---------- ---------- --------------------------------------------------

         8          6 JYCTBS     /home/db/oracle/oradata/erp/JYCTBS03.dbf

         9          6 JYCTBS     /home/db/oracle/oradata/erp/JYCTBS02.dbf

        10          6 JYCTBS     /home/db/oracle/oradata/erp/JYCTBS01.dbf

         4          4 PARTTBS1   /home/db/oracle/oradata/erp/PARTTBS103.dbf

         5          4 PARTTBS1   /home/db/oracle/oradata/erp/PARTTBS102.dbf

         6          4 PARTTBS1   /home/db/oracle/oradata/erp/PARTTBS101.dbf

         2          1 SYSAUX     /home/db/oracle/oradata/erp/sysaux01.dbf

         1          0 SYSTEM     /home/db/oracle/oradata/erp/system01.dbf

         3          2 UNDOTBS1   /home/db/oracle/oradata/erp/undotbs1.dbf

         7          5 USERS      /home/db/oracle/oradata/erp/USERS01.dbf

 

10 rows selected.

 

目标端：修改表空间可读写

SQL> alter tablespace jyctbs read write;

 

Tablespace altered.

 

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

PARTTBS1                       ONLINE

USERS                          ONLINE

JYCTBS                         ONLINE

 

7 rows selected.

 

SQL> show user;   

USER is "JYC"

SQL> insert into t1 values('222');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select count(*) from t1;

 

  COUNT(*)

----------

     11003

 

SQL> show parameter recy;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_recycle                  string

db_recycle_cache_size                big integer 0

recyclebin                           string      on

目标端：收集统计信息

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'JYC',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>2);