Shulou(Shulou.com)05/31 Report--

This article mainly introduces how Oracle 12C uses backup sets to perform cross-platform transfer of read-only tablespaces. This article is very detailed and has a certain reference value. Interested friends must finish reading it!

Use the backup command with for transport or to platform clauses to create a backup set to transfer read-only tablespaces from one platform to another. When you transfer read-only tablespaces, you must export metadata for those tablespaces. This metadata is needed when attaching tablespaces to the target database. When transporting tablespaces across platforms, the source and target platforms can use different byte orders. You can restore all or some of the data files or tablespaces in a cross-platform backup. After you restore these objects, you can specify a path and name for the restored data file.

Prerequisites for transporting tablespaces across platforms using backup sets

. The compatible parameter in the spfile parameter file of the source and target databases must be set to 12.0.0 or later.

. The tablespace being transferred must be self-contained. Perform the dbms_tts.transport_set_check procedure to check for dependencies. If the transport_set_violations view contains related row records for the specified tablespace, this dependency must be resolved before creating a cross-platform backup.

. The tablespace being transferred must be in read-only mode unless the allow inconsistent clause is used in the backup command.

The following example transfers the tablespaces tts and cs of jypdb in RAC CDB on the Linux platform to the jypdb database of the single instance CDB on the windows platform. Using backup sets to perform cross-platform transfers on read-only tablespaces is as follows:

1. Check whether the compatible parameter of the source and target databases is set to 12.0.0 or later

Source database

SQL > show parameter compatibleNAME TYPE VALUE-- compatible string 12.2.0noncdb_compatible boolean FALSE target database SQL > show parameter compatibleNAME TYPE VALUE -compatible string 12.2.0noncdb_compatible boolean FALSE

two。 Perform a self-inclusion check on the tablespaces tts and cs to be transferred in the source database

SQL > alter session set container=jypdb;Session altered.SQL > exec sys.dbms_tts.transport_set_check ('TTS,CS',true); PL/SQL procedure successfully completed.SQL > select * from sys.transport_set_violations;no rows selected

3. Check the byte order of the source and target platforms

Source platform

SQL > select d.platformnamename endianned format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name PLATFORM_NAME ENDIAN_FORMAT -Linux x86 64-bit Little

Target platform

SQL > select d.platformidnamereendianshaped format from v$transportable_platform tp V$database d where tPLATFORM_NAME ENDIAN_FORMAT -Microsoft Windows x86 64-bit Little

4. The tablespace to be transferred (tts,cs) is set to read-only

SQL > alter tablespace tts read only;Tablespace altered.SQL > alter tablespace cs read only;Tablespace altered.SQL > select tablespace_name,status from user_tablespaces TABLESPACE_NAME STATUS---SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUNDO_2 ONLINEUSERS ONLINETEST ONLINETTS READ ONLYCS READ ONLY9 rows selected.

5. Choose a naming method for the RMAN backup output file and use the format clause of the backup command to select the output method for the backup output file.

6. Start RMAN to connect to the source database (the entire RAC CDB) and restart the source database and open it as read-only

[oracle@jytest1] $rman target sys/abcd@jy catalog rco/abcd@jypdb_173Recovery Manager: Release 12.2.0.1.0-Production on Mon Feb 5 23:22:24 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.connected to target database: JY (DBID=979425723) connected to recovery catalog database

7. Use the for transport or to platform clause in the backup command to back up the tablespace. The following example creates a cross-platform transport backup for tablespaces tts and cs. Backups can be restored to the Microsoft Windows IA (64-bit) platform. The Data Pump export dump file contains the metadata needed to attach tablespaces to the target database. Backup files and dump files are stored in the / ora_backup/tts directory.

RMAN > backup to platform 'Microsoft Windows x86 64 format / ora_backup/tts/trans_ts.bck' datapump format / ora_backup/tts/trans_ts_dmp.bck' tablespace JYPDB:'TTS',JYPDB:'CS';Starting backup at 05-FEB-18using channel ORA_DISK_1Running TRANSPORT_SET_CHECK on specified tablespacesTRANSPORT_SET_CHECK completed successfullyPerforming export of metadata for specified tablespaces... EXPDP > Starting "SYS". "TRANSPORT_EXP_JY_xput": EXPDP > Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS EXPDP > Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS EXPDP > Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER EXPDP > Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK EXPDP > Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK EXPDP > Processing object type TRANSPORTABLE_EXPORT/TABLE EXPDP > Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX EXPDP > Master table "SYS". "TRANSPORT_EXP_JY_xput" Successfully loaded/unloaded EXPDP > * EXPDP > Dump file set for SYS.TRANSPORT_EXP_JY_xput is: EXPDP > / u01/app/oracle/product/12.2.0/db / dbs/backup_tts_JY_38873.dmp EXPDP > * EXPDP > Datafiles required for transportable tablespace CS: EXPDP > + DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783 EXPDP > Datafiles Required for transportable tablespace TTS: EXPDP > + DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353 EXPDP > Job "SYS". "TRANSPORT_EXP_JY_xput" successfully completed at Mon Feb 5 23:57:57 2018 elapsed: 03:08Export completedchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783channel ORA_DISK_1 : starting piece 1 at 05-FEB-18channel ORA_DISK_1: finished piece 1 at 05-FEB-18piece handle=/ora_backup/tts/trans_ts.bck tag=TAG20180205T235319 comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setinput Data Pump dump file=/u01/app/oracle/product/12.2.0/db/dbs/backup_tts_JY_38873.dmpchannel ORA_DISK_1: starting piece 1 at 05-FEB-18channel ORA_DISK_1: finished piece 1 at 05-FEB-18piece handle=/ora_backup/tts/trans_ts_dmp.bck tag=TAG20180205T235319 comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01Finished backup at 05-FEB-18starting full resync of recovery catalogfull resync complete [oracle@jytest1 tts] $ls-lrttotal 2472 Murray r-1 oracle asmadmin 2318336 Feb 5 17:57 trans_ts.bck-rw-r- 1 oracle asmadmin 212992 Feb 5 17:57 trans_ts_dmp.bck

Because the to platform clause is used in the backup command, the conversion of the data file to the target platform byte order is performed on the source platform.

8. Transfer the backup set generated by the backup command and the dump file exported by Data Pump to the D:\ app\ oracle\ oradata\ tts directory of the target platform

9. Connect to the target database using rman (JYPDB in single instance CDB)

C:\ Users\ Administrator > rman target sys/abcd@jypdb recovery Manager: Release 12.2.0.1.0-Production on Monday February 5 00:22:31 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to the target database: JY:JYPDB (DBID=4203494064)

10. Use the restore command with the foreign tablespace clause to restore the backup set transferred from the source platform

RMAN > restore foreign tablespace TTS,CS to new from backupset'D:\ app\ oracle\ oradata\ tts\ trans_ts.bck' dump file from backupset'D:\ app\ oracle\ oradata\ tts\ trans_ts_dmp.bck' Starting from restore at 06-February-18, replace the channel assigned by the recovery directory with the target database control file: ORA_DISK_1 channel ORA_DISK_1: SID=8 device type = DISK channel ORA_DISK_1: starting to restore data file backup set channel ORA_DISK_1: specifying data file channel ORA_DISK_1: restoring external tablespaces All file channels in TTS ORA_DISK_1: restoring all file channels in external tablespace CS ORA_DISK_1: reading backup fragment D:\ app\ oracle\ oradata\ tts\ trans_ts.bck channel ORA_DISK_1: restore external file 22 to D:\ APP\ ORACLE\ ORADATA\ JY\ 5F9AC6865E87549FE053AB828A0ADE94\ DATAFILE\ O1_MF_TTS_F7K17RGW_.DBF channel ORA_DISK_1: external Restore file 23 to D:\ APP\ ORACLE\ ORADATA\ JY\ 5F9AC6865E87549FE053AB828A0ADE94\ DATAFILE\ O1_MF_CS_F7K17RHC_.DBF channel ORA_DISK_1: external fragment handle = D:\ app\ oracle\ oradata\ tts\ trans_ts.bck channel ORA_DISK_1: restored backup fragment 1 channel ORA_DISK_1: restore complete Time: 00:00:02 channel ORA_DISK_1: starting to restore data files backup set channel ORA_DISK_1: specifying data file channel to restore from backup set ORA_DISK_1: restoring data pump dump files to D:\ app\ oracle\ product\ 12.2.0\ dbhome_1/dbs/backup_tts_JY_38586.dmp channel ORA_DISK_1: reading Backup fragment D:\ app\ oracle\ oradata\ tts\ trans_ts_dmp.bck channel ORA_DISK_1: external fragment handle = D:\ app\ oracle\ oradata\ tts\ trans_ts_dmp.bck channel ORA_DISK_1: restored backup fragment 1 channel ORA_DISK_1: restore complete Usage: metadata import is being performed at 00:00:02. IMPDP > successfully loaded / unloaded the main table "SYS". "TSPITR_IMP_JY_pfCp" IMPDP > start "SYS". "TSPITR_IMP_JY_pfCp": IMPDP > processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK IMPDP > processing object type TRANSPORTABLE_EXPORT/TABLE IMPDP > processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX IMPDP > processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS 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 "SYS". "TSPITR_IMP_JY_pfCp" successfully completed import on Tuesday February 6 00:24:02 2018 elapsed 0 00:00:33 and completed restore in 05-February-18.

11. Query whether the table tts.t1 and cs.t2 records in the target database are consistent with those in the source database, and if the table space tts and cs are transferred successfully across platforms

Source database

The SQL > alter session set container=jypdb; session has changed. SQL > select count (*) from tts.t1; COUNT (*)-45SQL > select count (*) from cs.t2; COUNT (*)-46

Target database

The SQL > alter session set container=jypdb; session has changed. SQL > select count (*) from tts.t1; COUNT (*)-45SQL > select count (*) from cs.t2; COUNT (*)-46 is all the contents of the article "how to use backup sets to perform cross-platform transfer of read-only tablespaces in Oracle 12C". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.