手动XTTS跨平台跨版本增量传输表空间_ora-19722-程序员宅基地
技术标签： oracle
基本信息
|
|
主机 IP |
操作系统 |
数据库版本 |
数据库信息 |
备份目录 |
| 源端 |
192.168.52.145 |
windows2008 |
10.2.0.5 |
数据库名erp |
NFS挂接目标端 /backup
|
| 目标端 |
192.168.52.12 |
Centos7.7 |
11.2.0.4 |
测试目的：
检查确认saperp用户数据都在parttbs1的表空间中。
通过XTTS方法，将saperp用户的数据从10g迁移升级至11g环境。
查看用户数据分布表空间：
set line 132
set wrap off
col segment_name for a30
SELECT t.tablespace_name,SUM(bytes)/1024/1024 From dba_segments t
WHERE t.owner = 'SAPERP'
GROUP BY t.tablespace_name
ORDER BY SUM(bytes) desc;
SELECT t.tablespace_name,t.segment_name,SUM(bytes)/1024/1024 From dba_segments t
WHERE t.owner = 'SAPERP'
GROUP BY t.tablespace_name,t.segment_name
ORDER BY SUM(bytes) desc;
查看表空间包含的对象：
SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024/1024 From dba_segments t
WHERE t.tablespace_name = 'PARTTBS1'
GROUP BY t.owner,t.segment_name
ORDER BY SUM(bytes) desc;
SELECT t.owner,SUM(bytes)/1024/1024/1024 From dba_segments t
WHERE t.tablespace_name = 'PARTTBS1'
GROUP BY t.owner
ORDER BY SUM(bytes) desc;
测试步骤：
源端：表空间自包含确认：
SQL> conn /as sysdba
已连接。
SQL> EXEC sys.dbms_tts.transport_set_check('PARTTBS1',TRUE);
PL/SQL 过程已成功完成。
SQL> select * from transport_set_violations;
未选定行
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSAUX
SYSMAN SYSAUX
TEST USERS
SAPERP PARTTBS1
OUTLN SYSTEM
set line 132
set wrap off
col name for a50
col tbsname for a10
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;
源端挂接目标端NFS目录：
C:\Users\Administrator>mount -u:root -p:"password" 192.168.52.155:/backup E:
E: 现已成功连接到 192.168.52.155:/backup
命令已成功完成。
源端：0级备份需要传输的表空间：
run {
allocate channel t1 type disk;
backup incremental level=0 tablespace PARTTBS1 format '\\192.168.52.155\backup\parttbs1_%U' tag=parttbs1;
release channel t1;
}
RMAN> list backup tag=parttbs1;
备份集列表
===================
BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
8 Incr 0 1.83M DISK 00:00:00 15-1月 -20
BP 关键字: 8 状态: AVAILABLE 已压缩: NO 标记: PARTTBS1
段名:C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\PARTTBS1_0EUM27P7_1_1
备份集 8 中的数据文件列表
文件 LV 类型 Ckp SCN Ckp 时间 名称
---- -- ---- ---------- ---------- ----
5 0 Incr 409875 15-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS101.DBF
7 0 Incr 409875 15-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS102.DBF
8 0 Incr 409875 15-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS103.DBF
BS 关键字 类型 LV 大小 设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
10 Incr 0 1.83M DISK 00:00:00 15-1月 -20
BP 关键字: 10 状态: AVAILABLE 已压缩: NO 标记: PARTTBS1
段名:\\192.168.52.155\BACKUP\PARTTBS1_0KUM286Q_1_1
备份集 10 中的数据文件列表
文件 LV 类型 Ckp SCN Ckp 时间 名称
---- -- ---- ---------- ---------- ----
5 0 Incr 410280 15-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS101.DBF
7 0 Incr 410280 15-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS102.DBF
8 0 Incr 410280 15-1月 -20 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ERP\PARTTBS103.DBF
目标端：做备份集转换格式(不同字节顺序平台需要做，同字节顺序平台，比如源windows->linux不需要此步骤)
DECLARE
handle varchar2(512);
comment varchar2(80);
media varchar2(80);
concur boolean;
recid number;
stamp number; pltfrmfr number;
devtype VARCHAR2(512); BEGIN
BEGIN
sys.dbms_backup_restore.restoreCancel(TRUE);
devtype := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.backupBackupPiece(bpname => '/backup/PARTTBS1_0KUM286Q_1_1',fname => '/backup/PARTTBS1_XTTS1',handle => handle,media=> media,comment=> comment, concur=> concur,recid=> recid,stamp => stamp, check_logical => FALSE,copyno=> 1, deffmt=> 0, copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=> 13);
END;
END;
/
目标端：提取0级备份文件:
在nomount状态下即可操作
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=>05,toname=>'/home/db/oracle/oradata/erp/PARTTBS101.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'/home/db/oracle/oradata/erp/PARTTBS102.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'/home/db/oracle/oradata/erp/PARTTBS103.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/backup/PARTTBS1_0KUM286Q_1_1', params=>null);
sys.dbms_backup_restore.deviceDeallocate;END;
/
或者使用转换过的备份集(不同字节顺序的时候需要用此)
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=>05,toname=>'/home/db/oracle/oradata/erp/PARTTBS101.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'/home/db/oracle/oradata/erp/PARTTBS102.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'/home/db/oracle/oradata/erp/PARTTBS103.dbf');
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/backup/PARTTBS1_XTTS1', params=>null);
sys.dbms_backup_restore.deviceDeallocate;END;
/
在/home/db/oracle/oradata/erp/目录下将看到提取的文件。
源端：1级增量备份
rman target /
run {
allocate channel t1 type disk;
backup incremental level=1 tablespace PARTTBS1 format '\\192.168.52.155\backup\parttbs1_%U' tag=parttbs11;
release channel t1;
}
目标端：第1次增量应用
set serveroutput on;
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 => 5 ,toname => '/home/db/oracle/oradata/erp/PARTTBS101.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 => 7 ,toname => '/home/db/oracle/oradata/erp/PARTTBS102.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 => 8 ,toname => '/home/db/oracle/oradata/erp/PARTTBS103.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/PARTTBS1_0MUM47DC_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;
/
源端：增加测试表
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSAUX
SYSMAN SYSAUX
TEST USERS
SAPERP PARTTBS1
OUTLN SYSTEM
TSMSYS USERS
DIP USERS
ORACLE_OCM USERS
已选择11行。
SQL> conn saperp/oracle
已连接。
SQL> create table jyc as select * from user_objects;
表已创建。
SQL> select count(*) from jyc;
COUNT(*)
----------
6
源端：再次1级增量备份：
run {
allocate channel t1 type disk;
backup incremental level=1 tablespace PARTTBS1 format '\\192.168.52.155\backup\parttbs1_%U' tag=parttbs11;
release channel t1;
}
目标端：第2次增量应用
set serveroutput on;
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 => 5 ,toname => '/home/db/oracle/oradata/erp/PARTTBS101.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 => 7 ,toname => '/home/db/oracle/oradata/erp/PARTTBS102.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 => 8 ,toname => '/home/db/oracle/oradata/erp/PARTTBS103.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/PARTTBS1_0NUM47OF_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;
/
注意：根据数据量的变化大小，增量备份测试和增量恢复可做多次，以此减少停机时间。
源端：导出创建用户的权限和角色
DECLARE
--多个用户使用 ; 隔开
v_users VARCHAR2(1000) := UPPER('SAPERP');
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;
/
----------------------华丽丽地分割线----------------------------------
--用户SAPERP脚本
--创建用户
CREATE USER "SAPERP" IDENTIFIED BY VALUES '4F008CCD13B6DF5C'
DEFAULT
TABLESPACE "PARTTBS1"
TEMPORARY TABLESPACE "TEMP"
;
--系统权限
GRANT UNLIMITED TABLESPACE TO "SAPERP";
;
--角色权限
GRANT "DBA" TO "SAPERP";
;
GRANT "SELECT_CATALOG_ROLE" TO "SAPERP";
;
--对象权限
Exception!
PL/SQL 过程已成功完成。
或者验证:
column Grantee format a10;
column Privilege format a25;
column Type format a5;
Select Pri.Grantee,
Pri.Privilege,
Pri.Admin_Option,
Case
When Type = '1' Then
'Privs'
When Type = '2' Then
'Role'
End Type
From (Select Sp.Grantee, Sp.Privilege, Sp.Admin_Option, '1' Type
From Dba_Sys_Privs Sp
Union All
Select Rp.Grantee, Rp.Granted_Role, Rp.Admin_Option, '2' Type
From Dba_Role_Privs Rp) Pri
Where Pri.Grantee = 'SAPERP';
目标端：调整脚本，创建用户：此时没有相应表空间。
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
SQL> CREATE USER SAPERP IDENTIFIED BY oracle DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP ;
User created.
SQL> GRANT UNLIMITED TABLESPACE TO "SAPERP";
Grant succeeded.
SQL> GRANT "DBA" TO "SAPERP";
Grant succeeded.
SQL> GRANT "SELECT_CATALOG_ROLE" TO "SAPERP";
Grant succeeded.
SQL>
停业务割接:
源端：设置只读表空间
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on 星期四 1月 16 10:54:00 2020
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba
已连接。
SQL> alter tablespace parttbs1 read only;
表空间已更改。
SQL> exit
源端：导出表空间元数据
从 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
C:\Users\Administrator>exp "'sys/oracle as sysdba'" transport_tablespace=y tablespaces=('PARTTBS1')
file=\\192.168.52.155\backup\PARTTBS1.dmp log=\\192.168.52.155\backup\PARTTBS1.log
Export: Release 10.2.0.5.0 - Production on 星期四 1月 16 10:54:18 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 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
对于表空间 PARTTBS1...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表 ERP
. . 正在导出表 T
. . 正在导出表 ERP2
. . 正在导出表 ERP3
. . 正在导出表 JYC
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
成功终止导出, 没有出现警告。
C:\Users\Administrator>
源端：最后做1级增量备份：
run {
allocate channel t1 type disk;
backup incremental level=1 tablespace PARTTBS1 format '\\192.168.52.155\backup\parttbs1_%U' tag=parttbs11;
release channel t1;
}
目标端：最后增量应用
set serveroutput on;
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 => 5 ,toname => '/home/db/oracle/oradata/erp/PARTTBS101.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 => 7 ,toname => '/home/db/oracle/oradata/erp/PARTTBS102.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 => 8 ,toname => '/home/db/oracle/oradata/erp/PARTTBS103.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/PARTTBS1_0OUM49AT_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;
/
[oracle@saperp backup]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 16 11:19:19 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> set serveroutput on;
SQL> DECLARE
2 outhandle varchar2(512) ;
3 outtag varchar2(30) ;
4 done boolean ;
5 failover boolean ;
6 devtype VARCHAR2(512);
7 BEGIN
8 DBMS_OUTPUT.put_line('Entering RollForward');
9 -- Now the rolling forward.
10 devtype := sys.dbms_backup_restore.deviceAllocate;
11 sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE) ;
12 DBMS_OUTPUT.put_line('After applySetDataFile');
13 sys.dbms_backup_restore.applyDatafileTo(dfnumber => 5 ,toname => '/home/db/oracle/oradata/erp/PARTTBS101.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
14 DBMS_OUTPUT.put_line('Done: applyDataFileTo');
15 sys.dbms_backup_restore.applyDatafileTo(dfnumber => 7 ,toname => '/home/db/oracle/oradata/erp/PARTTBS102.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
16 DBMS_OUTPUT.put_line('Done: applyDataFileTo');
17 sys.dbms_backup_restore.applyDatafileTo(dfnumber => 8 ,toname => '/home/db/oracle/oradata/erp/PARTTBS103.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0);
18 DBMS_OUTPUT.put_line('Done: applyDataFileTo');
19 -- Restore Set Piece
20 sys.dbms_backup_restore.restoreSetPiece(handle => '/backup/PARTTBS1_0OUM49AT_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ;
21 DBMS_OUTPUT.put_line('Done: RestoreSetPiece');
22 -- Restore Backup Piece
23 sys.dbms_backup_restore.restoreBackupPiece(done => done, params => null, outhandle => outhandle,outtag => outtag, failover => failover);
24 DBMS_OUTPUT.put_line('Done: RestoreBackupPiece');
25 sys.dbms_backup_restore.restoreCancel(TRUE);
26 sys.dbms_backup_restore.deviceDeallocate;
27 END;
28 /
Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
SQL> exit
目标端：imp导入表空间元数据
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp backup]$ imp "'sys/oracle as sysdba'" transport_tablespace=y TABLESPACES='PARTTBS1' file=PARTTBS1.dmp log=PARTTBS1-imp.log datafiles='/home/db/oracle/oradata/erp/PARTTBS101.dbf','/home/db/oracle/oradata/erp/PARTTBS102.dbf','/home/db/oracle/oradata/erp/PARTTBS103.dbf'
Import: Release 11.2.0.4.0 - Production on Thu Jan 16 11:19:41 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 SAPERP's objects into SAPERP
. . importing table "ERP"
. . importing table "T"
. . importing table "ERP2"
. . importing table "ERP3"
. . importing table "JYC"
. 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 Thu Jan 16 11:19:54 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select username,default_tablespace from dba_users where username='SAPERP';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SAPERP SYSTEM
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
PARTTBS1 READ ONLY
还原默认表空间
SQL> alter user saperp default tablespace parttbs1;
User altered.
SQL> select username,default_tablespace from dba_users where username='SAPERP';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SAPERP PARTTBS1
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
PARTTBS1 READ ONLY
SQL> alter tablespace PARTTBS1 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
SQL> conn saperp/oracle
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
JYC
ERP3
ERP2
T
ERP
SQL> select count(*) from jyc;
COUNT(*)
----------
6
SQL> select count(*) from erp3;
COUNT(*)
----------
10992
SQL> select count(*) from erp2;
COUNT(*)
----------
10991
SQL> select count(*) from t;
COUNT(*)
----------
1
SQL> select count(*) from erp;
COUNT(*)
----------
2
目标端：校验表空间是否损坏
validate tablespace PARTTBS1 check logical;
[oracle@saperp admin]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 16 12:17:17 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ERP (DBID=278491646)
RMAN> validate tablespace PARTTBS1 check logical;
Starting validate at 2020-01-16 12:17:20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/home/db/oracle/oradata/erp/PARTTBS103.dbf
input datafile file number=00005 name=/home/db/oracle/oradata/erp/PARTTBS102.dbf
input datafile file number=00006 name=/home/db/oracle/oradata/erp/PARTTBS101.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 12769 12800 400134
File Name: /home/db/oracle/oradata/erp/PARTTBS103.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21
Index 0 0
Other 0 10
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 12731 12800 453883
File Name: /home/db/oracle/oradata/erp/PARTTBS102.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 52
Index 0 0
Other 0 17
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 12668 12800 400147
File Name: /home/db/oracle/oradata/erp/PARTTBS101.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 109
Index 0 0
Other 0 23
Finished validate at 2020-01-16 12:17:22
RMAN>
目标端：编译失效对象
[oracle@saperp admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 16 12:20:35 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-01-16 12:20:41
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2020-01-16 12:20:42
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>
目标端：收集统计信息：
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SAPERP',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>2);
PL/SQL procedure successfully completed.
遇到问题：
1.手动创建数据库完成后，创建新用户，并用新用户登陆到数据库时，报错如下：
SQL> conn saperp/oracle
ERROR:
ORA-00942: table or view does not exist
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL>
使用SYSDBA登陆时无报错。
解决方法：
需要使用SYSTEM用户登陆并执行$ORACLE_HOME/sqlplus/admin/pupbld.sql脚本，不能用SYSDBA。
SQL> conn system/oracle
ERROR:
ORA-01017: 用户名/口令无效; 登录被拒绝
SQL> conn /as sysdba
Connected.
SQL> alter user system identified by oracle;
User altered.
SQL> conn system/oracle
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp backup]$ cd $ORACLE_HOME/sqlplus
[oracle@saperp sqlplus]$ cd admin
[oracle@saperp admin]$ ls *.sql
glogin.sql plustrce.sql pupbld.sql
[oracle@saperp admin]$ pwd
/home/db/oracle/product/11.2.0/db_1/sqlplus/admin
[oracle@saperp admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 16 11:26:44 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn system/oracle
Connected.
SQL> @/home/db/oracle/product/11.2.0/db_1/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
DATE_VALUE FROM PRODUCT_USER_PROFILE
*
ERROR at line 3:
ORA-00942: table or view does not exist
DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
DROP PUBLIC SYNONYM PRODUCT_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
2.如果源端没有设置只读表空间，目标端导入元数据报错：
导入:
[oracle@saperp backup]$ imp "'sys/oracle as sysdba'" transport_tablespace=y TABLESPACES='PARTTBS1' file=PARTTBS1.dmp log=PARTTBS1-imp.log datafiles='/home/db/oracle/oradata/erp/PARTTBS101.dbf','/home/db/oracle/oradata/erp/PARTTBS102.dbf','/home/db/oracle/oradata/erp/PARTTBS103.dbf'
Import: Release 11.2.0.4.0 - Production on Thu Jan 16 11:15:57 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
IMP-00017: following statement failed with ORACLE error 19722:
"BEGIN sys.dbms_plugts.checkDatafile(NULL,278480429,5,12800,5,5,0,0,399514"
",454152,1,20971522,NULL,NULL,NULL); END;"
IMP-00003: ORACLE error 19722 encountered
ORA-19722: datafile /home/db/oracle/oradata/erp/PARTTBS101.dbf is an incorrect version
ORA-06512: at "SYS.DBMS_PLUGTS", line 2680
ORA-06512: at "SYS.DBMS_PLUGTS", line 2228
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
[oracle@saperp backup]$ oerr ora 19722
19722, 00000, "datafile %s is an incorrect version"
// *Cause: The datafile is an incorrect version. It contains either less or
// more changes then the desired version.
// *Action: Make sure the right datafiles are transported. Make sure the
// datafile is copied while its tablespace is read only.
3.对于其它用户的迁移
可以采用expdp/impdp的方式即可。
expdp \'/ as sysdba\' DIRECTORY=dmp schemas=TEST PARALLEL=4 CLUSTER=N DUMPFILE=TEST-`date "+%Y%m%d_%H%M%S"`_%U.dmp logfile=TEST-`date "+%Y%m%d_%H%M%S"`.log exclude=statistics
智能推荐
气泡提示浮出层的两种纯CSS写法(浏览器全兼容)-程序员宅基地
文章浏览阅读153次。气泡提示能给用户良好的浏览体验，相信大部分前端人都做过吧？(什么？你没做过，那信息提示层总做过吧？！)最近在网上看到了一种写气泡提示的纯CSS代码，其代码简练，兼容性之强，是非常少见的，效果如下图。首先，是第一种，利用字符“◆”实现。请看代码，HTML部分：1234<div class="poptip...
Android————————Handler-程序员宅基地
文章浏览阅读325次。转自：http://www.cnblogs.com/devinzhang/archive/2011/12/30/2306980.html方法一：(java习惯，在android平台开发时这样是不行的，因为它违背了单线程模型）刚刚开始接触android线程编程的时候，习惯好像java一样，试图用下面的代码解决问题 new Thread( new Runnabl
钉钉应用中H5开发适配iphonex_钉钉h5微应用如何判断是否为苹果系统-程序员宅基地
文章浏览阅读2.1k次。钉钉容器中开发H5应用时适配iphonex首先要了解iphonex屏幕尺寸的一些知识,可以参考H5页面WebView在Iphonex的适配,有了对iphonex屏幕的基本认识后,适配就相对简单一些了.盗图一张:bottom部分fixed定位,底部会造成镂空,此时我们在viewport中加入viewport-fit=cover,html和body设置高度100%,可以将bottom部分下移到..._钉钉h5微应用如何判断是否为苹果系统
期望、方差_期望和方差-程序员宅基地
文章浏览阅读4.5w次，点赞22次，收藏113次。一、期望和方差的定义随机变量(Random Variable) X 是一个映射，把随机试验的结果与实数建立起了一一对应的关系。而期望与方差是随机变量的两个重要的数字特征。1. 期望(Expectation, or expected value)期望是度量一个随机变量取值的集中位置或平均水平的最基本的数字特征；2. 方差(Variance)方差是表示随机变量取值的分散性的一个数字特征。 方差越大，说明随机变量的取值分布越不均匀，变化性越强；方差越小，说明随机变量的取值越趋近于均值，即期望值。（1_期望和方差
DPC-Clustering by fast search and find of density peaks 2014_dpcluster-程序员宅基地
文章浏览阅读143次。步骤：1 计算两个参数（每个点的密度，每个点的deltai）2 画图（决策图）3 人工筛选聚类中心，然后把剩余点归到离自己最近的中心（1）密度：两种计算方式：基于截断核的计算+基于高斯核的计算方式截断核的计算：画个半径数个数高斯核的计算：高斯模型两种方式对比：使用截断核计算得到的结果为离散值而使用高斯核计算得到的结果为连续值。因此，相对而言，使用高斯核计算得到的数据点具有相同的局部密度的概率比使用截断核计算得到的数据点具有相同的局部密度的概率要小，比较密度大小关系更方便（2）deltai（与_dpcluster
gmapping应用详解_运行gmapping-程序员宅基地
文章浏览阅读6k次。gmapping是一个比较完善的地图构建开源包，使用激光和里程计的数据来生成二维地图。 博主最近也一直在使用gmapping，在前期着实被它卡了好久，因为好多东西不明白（原谅我是ros小白）。 现在把最近的一些收获分享一下。一、gmapping应用条件想使用gmapping包，当然要清楚它的输入输出是什么。 在ros中这些输入输出通常表现为订阅（subscribe）和发_运行gmapping
随便推点
Android Jetpack--lifecycle全解析_jetpack lifecycle-程序员宅基地
文章浏览阅读1.9k次，点赞4次，收藏5次。1、概述google的jetpack已经出来很久了，最近项目中用接入后，也是遇到了不少坑。于是抽时间好好研究了下lifecycle、livedata、viewModel等源码，本文就来介绍一下lifecycle的用法及原理。2、设计初衷先来一句官方解释：android.arch.lifecycle 包提供了可以用于构建生命周期感知（lifecycle-aware）组件的类和接口，这些组件可以..._jetpack lifecycle
遍历HashMap和ArrayList_list<hashmap>-程序员宅基地
文章浏览阅读632次，点赞2次，收藏9次。一、遍历HashMap1、第一种方式 ：keySet()得到所有键的集合存储在set中，存入set集合，set有迭代器，使用迭代器每次得到一个键，根据键获取值。Map map1=new HashMap();map1.put("04", new User("张一","111",18));map1.put("05", new User("张二","222",19));map1._list
CAD转GIS工具，路径1——python/arcpy读取dxf获取用地hatch生成面_python ezdxf 获取图层数据_规划酱的博客-程序员宅基地
文章浏览阅读1.3k次。用地CAD转GIS一直都是老大难的问题，主要办法是通过FME等工具。GIS中读取的CAD是分为点、线、面几个图层，与GSI的数据集分类是一致的，这个里面并没有填充面。基于ArcGIS的转换有两个思路，一是读取dxf文件中的hatch信息，然后在GIS中创建面。二是通过GIS打开DWG，读取其中面相关的信息，创建面。读取dxf文件DXF是AutoCAD 绘图交换文件。DXF 是Autodesk（欧特克）公司开发的用于AutoCAD与其它软件之间进行CAD数据交换的CAD数据文件格式。DXF文件由_python ezdxf 获取图层数据
WIN下安装TensorFlow-程序员宅基地
文章浏览阅读280次。爬坑
杂文——代码表白-程序员宅基地
文章浏览阅读213次。最近“偶然间”发现了一篇知乎文章，是关于代码表白的，代码表白-知乎看完后来了兴趣，作为程序猿， 怎么能不来写一手代码表白呢？小露身手：感觉还行，这是配上注释后的完整版本：...
鸿蒙开源系统还需要授权吗,鸿蒙操作系统开源是什么意思？华为鸿蒙系统什么时候能用？..._苟全性命的博客-程序员宅基地
文章浏览阅读839次。华为在广东东莞的华为松山湖基地召开了2019华为全球开发者大会，正式发布了自主研发的操作系统鸿蒙。余承东说：“通过一套系统，一个OS实现模块化解耦，对应不同设备可以弹性部署，通过操作系统分布式软总线让同一个帐户下的多种终端能够实现硬件能力跨终端之间调用。我们手表上没有摄像头，可以用手机的摄像头;我们觉得PC的摄像头不好，可以用手机的，一个帐户共享互用，系统硬件解耦，弹性部署。同时希望让操作系统、让..._鸿蒙 需要微软授权吗