重庆思庄Oracle、Redhat认证学习论坛
标题: oracle12c 使用备份集完成表空间跨平台传输 [打印本页]
作者: 郑全 时间: 2019-8-25 13:30
标题: oracle12c 使用备份集完成表空间跨平台传输
环境：
源：orcl
目标：rcat
版本：12.1.0.2
为测试方便，在同一台机器，操作系统为 rhel7.6
在12c以前进行表空间传输，一般使用物理文件拷贝的居多，下面我们来使用要传输的表空间的备份集来完成传输
1.源端情况
SQL> select name,log_mode,open_mode,current_scn from v$database;
NAME LOG_MODE OPEN_MODE CURRENT_SCN
--------- ------------ -------------------- -----------
ORCL ARCHIVELOG READ WRITE 2272153
--如果使用全量备份，可以不用开归档
1* select platform_id,platform_name,endian_format from v$transportable_platform where upper(platform_name) like '%LINUX%'
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
18 IBM Power Based Linux Big
2.准备表空间及数据
mkdir /u01/backup/{orcl,rcat}
CREATE TABLESPACE bartbs
DATAFILE '/u01/backup/orcl/bartbs.dbf' SIZE 10M REUSE;
-- Create user
CREATE USER BAR IDENTIFIED BY oracle_4U
DEFAULT TABLESPACE bartbs
QUOTA UNLIMITED ON bartbs;
GRANT CREATE SESSION TO BAR;
-- create table and populate
-- be sure table is at least 2 blocks long
CREATE TABLE BAR.barcopy
TABLESPACE bartbs
AS SELECT * FROM HR.EMPLOYEES;
INSERT INTO BAR.BARCOPY
SELECT * FROM BAR.BARCOPY;
INSERT INTO BAR.BARCOPY
SELECT * FROM BAR.BARCOPY;
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_FDA ONLINE
BARTBS ONLINE
8 rows selected.
3.备份要传输的表空间bartbs及expdp元数据导出
backup to platform 'Linux x86 64-bit' FORMAT '/u01/backup/test.bck'
datapump format '/u01/backup/test.dmp' tablespace bartbs;
具体执行如下：
sql>alter tablespace bartbs read only;
[oracle@dbserver back]$ rman target "'/ as sysbackup'"
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Aug 25 11:53:14 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1544254351)
RMAN> backup to platform 'Linux x86 64-bit' FORMAT '/u01/backup/test.bck' datapump format '/u01/backup/test.dmp' tablespace bartbs;
Starting backup at 25-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully
Performing export of metadata for specified tablespaces...
EXPDP> Starting "SYSBACKUP"."TRANSPORT_EXP_ORCL_kemn":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYSBACKUP"."TRANSPORT_EXP_ORCL_kemn" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYSBACKUP.TRANSPORT_EXP_ORCL_kemn is:
EXPDP> /u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_ORCL_96001.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace BARTBS:
EXPDP> /u01/backup/orcl/bartbs.dbf
EXPDP> Job "SYSBACKUP"."TRANSPORT_EXP_ORCL_kemn" successfully completed at Sun Aug 25 11:55:38 2019 elapsed 0 00:01:04
Export completed
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/backup/orcl/bartbs.dbf
channel ORA_DISK_1: starting piece 1 at 25-AUG-19
channel ORA_DISK_1: finished piece 1 at 25-AUG-19
piece handle=/u01/backup/test.bck tag=TAG20190825T115424 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
input Data Pump dump file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_ORCL_96001.dmp
channel ORA_DISK_1: starting piece 1 at 25-AUG-19
channel ORA_DISK_1: finished piece 1 at 25-AUG-19
piece handle=/u01/backup/test.dmp tag=TAG20190825T115424 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-AUG-19
RMAN>
--查看生成的备份集及dmp元数据文件
[oracle@dbserver backup]$ pwd
/u01/backup
[oracle@dbserver backup]$ ls -ltr
total 1292
drwxr-xr-x 2 oracle oinstall 24 Aug 25 11:47 orcl
-rw-r----- 1 oracle asmadmin 1163264 Aug 25 12:04 test.bck
-rw-r----- 1 oracle asmadmin 159744 Aug 25 12:04 test.dmp
[oracle@dbserver backup]$
4. 恢复原库的表空间为读写
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_FDA ONLINE
BARTBS READ ONLY
8 rows selected.
SQL> alter tablespace bartbs read write;
Tablespace altered.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_FDA ONLINE
BARTBS ONLINE
8 rows selected.
SQL>
5.目标库准备
由于在同一台机器，备份集和dmp元数据文件就不用移动
对归没有归档没有硬性要求
SQL> select name,log_mode,open_mode,current_scn from v$database;
NAME LOG_MODE OPEN_MODE CURRENT_SCN
--------- ------------ -------------------- -----------
RCAT NOARCHIVELOG READ WRITE 1895900
--创建对应的用户 bar
SQL> create user bar identified by oracle_4U;
User created.
SQL> grant create session to bar;
Grant succeeded.
SQL>
6.目标库还原及dump导入
--登陆目标库
[oracle@dbserver backup]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Aug 25 12:10:10 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: RCAT (DBID=788438261)
RMAN>
RMAN> select name,log_mode,open_mode,current_scn from v$database;
using target database control file instead of recovery catalog
NAME LOG_MODE OPEN_MODE CURRENT_SCN
--------- ------------ -------------------- -----------
RCAT NOARCHIVELOG READ WRITE 1896056
--restore
RMAN> restore foreign tablespace bartbs
2> format '/u01/backup/rcat/bartbs.dbf' from backupset '/u01/backup/test.bck'
3> dump file from backupset '/u01/backup/test.dmp';
Starting restore at 25-AUG-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring all files in foreign tablespace BARTBS
channel ORA_DISK_1: reading from backup piece /u01/backup/test.bck
channel ORA_DISK_1: restoring foreign file 7 to /u01/backup/rcat/bartbs.dbf
channel ORA_DISK_1: foreign piece handle=/u01/backup/test.bck
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring Data Pump dump file to /u01/app/oracle/product/12.1.0/dbhome_1/dbs/backup_tts_RCAT_98140.dmp
channel ORA_DISK_1: reading from backup piece /u01/backup/test.dmp
channel ORA_DISK_1: foreign piece handle=/u01/backup/test.dmp
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Performing import of metadata...
IMPDP> Master table "SYSBACKUP"."TSPITR_IMP_RCAT_Fnao" successfully loaded/unloaded
IMPDP> Starting "SYSBACKUP"."TSPITR_IMP_RCAT_Fnao":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYSBACKUP"."TSPITR_IMP_RCAT_Fnao" successfully completed at Sun Aug 25 12:14:36 2019 elapsed 0 00:00:03
Import completed
Finished restore at 25-AUG-19
RMAN>
--
7.确认数据
--确认表空间状态为 read only
RMAN> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
BARTBS READ ONLY
RMAN>
SQL> conn bar/oracle_4U
Connected.
SQL> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
BARCOPY
SQL> select count(*) from barcopy;
COUNT(*)
----------
428
说明数据已经过来
8.表空间可读写
SQL> conn / as sysdba
Connected.
SQL> alter tablespace bartbs read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
BARTBS ONLINE
6 rows selected.
SQL>
作者: 郑全 时间: 2019-8-25 13:44
