Oracle 12.2 ORA-01113 ORA-01110 recover after normal shutdown

Oracle Linux 7.1数据库为Oracle 12.2.0.1 RAC,数据库启用了归档,在手动关闭数据库后启动数据库时提示PDB库有数据文件需要进行介质恢复

[grid@jytest1 ~]$ srvctl stop database -db jy
[grid@jytest1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.FRA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest2                  169.254.237.250 88.8
                                                             8.88.2,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  STABLE
      3        ONLINE  OFFLINE                               STABLE

ora.cvu
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.jy.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.jy.jy_srv.svc
      1        OFFLINE OFFLINE                               STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest2                  Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
--------------------------------------------------------------------------------

提示PDB的数据文件需要进行介质恢复,文件号是38

[grid@jytest1 ~]$ srvctl start database -db jy
PRCR-1079 : Failed to start resource ora.jy.db
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest2' failed
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest1' failed
CRS-2632: There are no more servers to try to place resource 'ora.jy.db' on that would satisfy its placement policy

对38号文件执行介质恢复

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 17:47:55 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1610616096 bytes
Database Buffers          520093696 bytes
Redo Buffers                7979008 bytes
Database mounted.
ORA-01113: file 38 needs media recovery
ORA-01110: data file 38:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649'


SQL> recover datafile 38;
Media recovery complete.

在对38号文件进行介质恢复后打开CDB时提示39号文件也需要进行介质恢复

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 39 needs media recovery
ORA-01110: data file 39:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649'

如是执行recover database命令来对CDB进行介质恢复并打开CDB

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

在打开JYPDB时提示PDB的45号文件需要进行介质恢复

SQL> alter pluggable database jypdb open;
alter pluggable database jypdb open
*
ERROR at line 1:
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'

如是对45号文件进行介质恢复,虽然介质恢复成功但在打开jypdb时仍然提示需要进行介质恢复

SQL>  recover datafile 45;
Media recovery complete.

SQL> alter pluggable database jypdb open;
alter pluggable database jypdb open
*
ERROR at line 1:
ORA-01113: file 45 needs media recovery
ORA-01110: data file 45:
'+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'

如是查询数据文件检查点SCN与数据文件头SCN,发现44与45号文件的数据文件检查点SCN与数据文件头SCN号不一致并且数据文件检查点SCN比数据文件头SCN号大,因此需要进行日志文件来进行恢复。但在执行recover datafile命令成功后,打开JYPDB时仍然提示需要进行介质恢复。

SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1          176117998
         3          176117998
         4          176117998
         5            1449535      1449535
         6            1449535      1449535
         7          176117998
         8            1449535      1449535
         9          176117998
        38          176098593    176098593
        39          176098593    176098593
        40          176098593    176098593

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
        41          176098593    176098593
        42          176098593    176098593
        43          176098593    176098593
        44          176098593    175898322
        45          176098593    175898322
        46           21664676     21664676
        47           21664676     21664676
        48           21664676     21664676
        49           21664676     21664676
        50           21664676     21664676
        51           21664676     21664676

22 rows selected.

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1          176117998
         3          176117998
         4          176117998
         5            1449535
         6            1449535
         7          176117998
         8            1449535
         9          176117998
        38          176098593
        39          176098593
        40          176098593

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
        41          176098593
        42          176098593
        43          176098593
        44          175898322
        45          175898322
        46           21664676
        47           21664676
        48           21664676
        49           21664676
        50           21664676
        51           21664676

22 rows selected.

如是打算重建控制文件再执行介质恢复,下面先备份控制文件到跟踪文件,在跟踪文件中有重建控制文件的相关命令

SQL> alter database backup controlfile to trace as '/tmp/ctl.txt';

Database altered.

关闭RAC数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

将RAC数据库启动到nomount状态

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1610616096 bytes
Database Buffers          520093696 bytes
Redo Buffers                7979008 bytes

重建控制文件,提示数据库不是排他模式,也就是说在RAC环境不能重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697'  SIZE 200M BLOCKSIZE 512,
 11    GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705'  SIZE 200M BLOCKSIZE 512,
 12    GROUP 6 (
 13      '+DATA/JY/ONLINELOG/group_6.280.972435899',
 14      '+FRA/JY/ONLINELOG/group_6.354.972435909'
 15    ) SIZE 200M BLOCKSIZE 512
 16  -- STANDBY LOGFILE
 17  DATAFILE
 18    '+DATA/JY/DATAFILE/system.317.962209603',
 19    '+DATA/JY/DATAFILE/sysaux.298.962209605',
 20    '+DATA/JY/DATAFILE/undotbs1.277.962209605',
 21    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
 22    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
 23    '+DATA/JY/DATAFILE/users.301.962209605',
 24    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
 25    '+DATA/JY/DATAFILE/undotbs2.312.962209605',
 26    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
 27    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
 28    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
 29    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
 30    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
 31    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
 32    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
 33    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783',
 34    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
 35    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
 36    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
 37    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
 38    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
 39    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409'
 40  CHARACTER SET ZHS16GBK
 41  ;
CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

修改数据库为排他模式

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cdb_cluster                          boolean     FALSE
cdb_cluster_name                     string      jy
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

重启数据库到nomount状态

SQL> shtudown immediate
SP2-0734: unknown command beginning "shtudown i..." - rest of line ignored.
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size            1459621152 bytes
Database Buffers          671088640 bytes
Redo Buffers                7979008 bytes

重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  9    GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512,
 10    GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697'  SIZE 200M BLOCKSIZE 512,
 11    GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705'  SIZE 200M BLOCKSIZE 512,
 12    GROUP 6 (
 13      '+DATA/JY/ONLINELOG/group_6.280.972435899',
 14      '+FRA/JY/ONLINELOG/group_6.354.972435909'
 15    ) SIZE 200M BLOCKSIZE 512
 16  -- STANDBY LOGFILE
 17  DATAFILE
 18    '+DATA/JY/DATAFILE/system.317.962209603',
 19    '+DATA/JY/DATAFILE/sysaux.298.962209605',
 20    '+DATA/JY/DATAFILE/undotbs1.277.962209605',
 21    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
 22    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
 23    '+DATA/JY/DATAFILE/users.301.962209605',
 24    '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
 25    '+DATA/JY/DATAFILE/undotbs2.312.962209605',
 26    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
 27    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
 28    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
 29    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
 30    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
 31    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
 32    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
 33    '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783',
 34    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
 35    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
 36    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
 37    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
 38    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
 39    '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409'
 40  CHARACTER SET ZHS16GBK
 41  ;

Control file created.

如是查询数据文件检查点SCN与数据文件头SCN,现在44与45号文件的数据文件检查点SCN与数据文件头SCN号仍然不一致并且数据文件检查点SCN比数据文件头SCN号大

SQL> col name for a100
SQL> select name,checkpoint_change# from v$datafile_header;

NAME                                                                                                 CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
+DATA/JY/DATAFILE/system.317.962209603                                                                        176144167
+DATA/JY/DATAFILE/sysaux.298.962209605                                                                        176144167
+DATA/JY/DATAFILE/undotbs1.277.962209605                                                                      176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675                                         1449535
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675                                         1449535
+DATA/JY/DATAFILE/users.301.962209605                                                                         176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675                                       1449535
+DATA/JY/DATAFILE/undotbs2.312.962209605                                                                      176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649                                     176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649                                       176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649                                        176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609                                         176098593
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353                                          175898322
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783                                           175898322
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409                                      21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409                                        21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409                                         21664676
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409                                          21664676

22 rows selected.

SQL> select name,checkpoint_change# from v$datafile;

NAME                                                                                                 CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
+DATA/JY/DATAFILE/system.317.962209603                                                                        176144167
+DATA/JY/DATAFILE/sysaux.298.962209605                                                                        176144167
+DATA/JY/DATAFILE/undotbs1.277.962209605                                                                      176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675                                       176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675                                       176144167
+DATA/JY/DATAFILE/users.301.962209605                                                                         176144167
+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675                                     176144167
+DATA/JY/DATAFILE/undotbs2.312.962209605                                                                      176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649                                     176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649                                       176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649                                        176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609                                         176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353                                          176144167
+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783                                           176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409                                     176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409                                       176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409                                        176144167
+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409                                         176144167

22 rows selected.

对CDB执行介质恢复操作,根据提示输入相关的归档日志文件名来进行恢复并将CDB成功打开

SQL> recover database
ORA-00279: change 175898322 generated at  needed for thread 2


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/JY/ARCHIVELOG/2019_02_13/thread_2_seq_1073.422.1000144183
ORA-00279: change 176098395 generated at 02/13/2019 17:49:43 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1074_961976319.dbf
ORA-00280: change 176098395 for thread 2 is in sequence #1074


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/JY/ARCHIVELOG/2019_02_13/thread_2_seq_1074.382.1000144185
ORA-00279: change 176098399 generated at 02/13/2019 17:49:45 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1075_961976319.dbf
ORA-00280: change 176098399 for thread 2 is in sequence #1075


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/arch/2_1075_961976319.dbf
ORA-00279: change 176098399 generated at 02/13/2019 17:49:45 needed for thread 1
ORA-00289: suggestion : +TEST/arch/1_1182_961976319.dbf
ORA-00280: change 176098399 for thread 1 is in sequence #1182


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/arch/1_1182_961976319.dbf
ORA-00279: change 176098401 generated at 02/13/2019 17:49:45 needed for thread 2
ORA-00289: suggestion : +TEST/arch/2_1076_961976319.dbf
ORA-00280: change 176098401 for thread 2 is in sequence #1076


Specify log: {=suggested | filename | AUTO | CANCEL}
 +TEST/arch/2_1076_961976319.dbf
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
ORA-00278: log file '+TEST/arch/2_1076_961976319.dbf' no longer needed for this recovery


ORA-00279: change 176098489 generated at 02/13/2019 17:49:50 needed for thread 1
ORA-00289: suggestion : +TEST/arch/1_1183_961976319.dbf
ORA-00280: change 176098489 for thread 1 is in sequence #1183


Specify log: {=suggested | filename | AUTO | CANCEL}
+TEST/arch/1_1183_961976319.dbf
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

将PDB数据库打开

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          READ WRITE NO

根据备份控制文件到跟踪文件重建控制文件的命令来添加临时表空间的相关文件

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/TEMPFILE/temp.299.961976339';

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = PDB$SEED;

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/TEMPFILE/temp.297.962209865'  SIZE 67108864  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = JYPDB;

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/TEMPFILE/temp.276.967852391' REUSE;

Tablespace altered.

将数据库设置为集群模式并关闭数据库

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

使用集群命令来启动数据库

[grid@jytest1 ~]$ srvctl start database -db jy

将JYPDB以读写方式打开

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 22:30:53 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          MOUNTED
SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          READ WRITE NO


[oracle@jytest2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 13 22:31:17 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          MOUNTED
SQL> alter pluggable database jypdb open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 JYPDB                          READ WRITE NO

到此数据库的恢复操作就完成了

Oracle 12c 使用RMAN搭建物理备库(RAC to RAC)

一.实例环境说明
主库环境说明
操作系统:Oracle Linux 7.1
数据库版本:12.2.0.1
IP:10.10.10.190/10.10.10.191
主机名:cs1/cs2
数据库名:cs
Db_unique_name:cs
实例名(SID)cs1/cs2

备库环境说明
操作系统:Oracle Linux 7.1
数据库版本:12.2.0.1
IP:10.10.10.171/172
主机名:jytest1/jytest2
数据库名:cs
Db_unique_nmae:cs_dg
实例名(SID)cs1/cs2

二.配置过程
2.1修改主库启用归档与force logging
首先检查主库是否启用归档

[oracle@cs1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Fri May 11 12:54:07 2018

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

SQL> conn sys/abcd@cs as sysdba
Connected.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/12.2.0/db/dbs/arch
Oldest online log sequence     135
Current log sequence           136
SQL>

[oracle@cs2 ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Fri May 11 12:54:22 2018

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

SQL> conn sys/abcd@cs as sysdba
Connected.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/12.2.0/db/dbs/arch
Oldest online log sequence     116
Current log sequence           117
SQL>

现在主库没有启用归档,执行下面的命令来对主库启用归档

SQL> alter system set log_archive_dest_1='location=+data/arch/' scope=both sid='*';

System altered.

SQL> show parameter log_archive_for

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> alter system set log_archive_dest_1='location=+data/arch/' scope=both sid='*';

System altered.

SQL> show parameter log_archive_for

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size             671092000 bytes
Database Buffers         1459617792 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/arch/
Oldest online log sequence     136
Next log sequence to archive   137
Current log sequence           137

这里主库已经启用了归档

启用force logging

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

2.2 给备库创建密码文件
这里通过复制主库的密码文件来创建备库密码文件

[oracle@jytest1 dbs]$ scp oracle@10.10.10.190:/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1 /u01/app/oracle/product/12.2.0/db/dbs/orapwcs2
The authenticity of host '10.10.10.190 (10.10.10.190)' can't be established.
ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.10.190' (ECDSA) to the list of known hosts.
oracle@10.10.10.190's password: 
orapwcs1                                                                                                                                                                                                  100% 3584     3.5KB/s   00:00    
[oracle@jytest2 dbs]$ ls -lrt


[oracle@jytest2 dbs]$ scp oracle@10.10.10.190:/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1 /u01/app/oracle/product/12.2.0/db/dbs/orapwcs2
The authenticity of host '10.10.10.190 (10.10.10.190)' can't be established.
ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.10.190' (ECDSA) to the list of known hosts.
oracle@10.10.10.190's password: 
orapwcs1                                                                                                                                                                                                  100% 3584     3.5KB/s   00:00    
[oracle@jytest2 dbs]$ ls -lrt

2.3.给备库创建参数文件
使用主库的参数文件进行创建

SQL> create pfile='/u01/app/oracle/product/12.2.0/db/dbs/initcs.ora' from spfile='+DATA/CS/PARAMETERFILE/spfile.287.970602765';

File created.

[oracle@jytest1 dbs]$ cat initcs1_temp.ora

*.audit_file_dest='/u01/app/oracle/admin/cs/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='+FRA/CS_DG/CONTROLFILE/control01.ctl'
*.db_block_size=8192
*.db_name='cs'
*.db_unique_name='cs_dg'
*.db_create_file_dest='+FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=csXDB)'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
log_archive_config='DG_CONFIG=(cs,cs_dg)'
cs2.instance_number=2
cs1.instance_number=1 
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_archive_dest_1='LOCATION=+test/arch/cs_dg/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=cs_dg'
*.log_archive_dest_2='service=cs LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=cs ' 
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1g
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=2g
cs2.thread=2
cs1.thread=1
cs2.undo_tablespace='UNDOTBS2'
cs1.undo_tablespace='UNDOTBS1'
*.db_file_name_convert= '+DATA/CS/','+FRA/CS_DG/'
*.fal_client='cs_dg'
*.fal_server='cs'
*.log_file_name_convert= '+DATA/CS/','+FRA/CS_DG/'
*.standby_file_management='auto'
SQL> create spfile='+fra/cs/parameterfile/spfilecs.ora' from pfile='/u01/app/oracle/product/12.2.0/db/dbs/initcs1_temp.ora';

File created.

[oracle@jytest1 dbs]$ vi initcs1.ora
spfile='+fra/cs/parameterfile/spfilecs.ora'

[oracle@jytest2 dbs]$ vi initcs2.ora
spfile='+fra/cs/parameterfile/spfilecs.ora'

2.4为主库和备库配置监听
主库是rac已经配置了监听,备库也是RAC需要对其配置监听
备库:节点1

[grid@jytest1 admin]$ vi listener.ora
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR))))            # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF             # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF             # line added by Agent

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = cs2)
      (ORACLE_HOME =/u01/app/oracle/product/12.2.0/db)
      (GLOBAL_DBNAME=cs_dg)
    )
  )

备库:节点2

[grid@jytest2 admin]$ vi listener.ora
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR))))            # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF             # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF             # line added by Agent

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = cs2)
      (ORACLE_HOME =/u01/app/oracle/product/12.2.0/db)
      (GLOBAL_DBNAME=cs_dg)
    )
  )
[grid@jytest1 admin]$ srvctl stop listener -n jytest1
[grid@jytest1 admin]$ srvctl stop listener -n jytest2
[grid@jytest1 admin]$ srvctl start listener -n jytest1
[grid@jytest1 admin]$ srvctl start listener -n jytest2

[grid@jytest1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-MAY-2018 22:33:06

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-MAY-2018 22:32:08
Uptime                    0 days 0 hr. 0 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.171)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.175)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "5f9ac6865e87549fe053ab828a0ade94" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "cs_dg" has 1 instance(s).
  Instance "cs1", status UNKNOWN, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jy_srv" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jypdb" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
The command completed successfully



[grid@jytest2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-MAY-2018 22:33:10

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-MAY-2018 22:32:16
Uptime                    0 days 0 hr. 0 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.172)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.176)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest2.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "5f9ac6865e87549fe053ab828a0ade94" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "cs_dg" has 1 instance(s).
  Instance "cs2", status UNKNOWN, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
Service "jypdb" has 1 instance(s).
  Instance "jy2", status READY, has 1 handler(s) for this service...
The command completed successfully

2.5为主库和备库创建Oracle Net服务名
主库:节点1

[oracle@cs1 admin]$ vi tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.149)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cs)
    )
  )
CS_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.175)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cs_dg)
      (UR=A)
    )
  )

主库:节点2

[oracle@cs2 admin]$ vi  tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.134)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cs)
    )
  )

CS_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.176)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cs_dg)
      (UR=A)
    )
  )

备库:节点1

[oracle@jytest1 admin]$ vi tnsnames.ora 
# tnsnames.ora.jytest1 Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora.jytest1
# Generated by Oracle configuration tools.
CS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.149)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cs)
    )
  )

备库:节点2

[oracle@jytest2 admin]$ vi tnsnames.ora 
# tnsnames.ora.jytest2 Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora.jytest2
# Generated by Oracle configuration tools.
CS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.134)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cs)
    )
  )

使用备份创建备库

[oracle@jytest1 admin]$ export ORACLE_SID=cs1
[oracle@jytest1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 00:06:43 2018

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  8794848 bytes
Variable Size             721423648 bytes
Database Buffers         1409286144 bytes
Redo Buffers                7979008 bytes
[oracle@cs1 admin]$ rman target sys/abcd@cs auxiliary sys/abcd@cs_dg

Recovery Manager: Release 12.2.0.1.0 - Production on Mon May 14 19:43:21 2018

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

connected to target database: CS (DBID=1386528187)
connected to auxiliary database: CS (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 14-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=197 instance=cs1 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATA/CS/PASSWORD/pwdcs.271.970601731' auxiliary format 
 '/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1'   ;
}
executing Memory Script

Starting backup at 14-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=291 instance=cs1 device type=DISK
Finished backup at 14-MAY-18

contents of Memory Script:
{
   restore clone from service  'cs' standby controlfile;
}
executing Memory Script

Starting restore at 14-MAY-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+FRA/CS_DG/CONTROLFILE/control01.ctl
Finished restore at 14-MAY-18

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+FRA";
   set newname for tempfile  2 to 
 "+FRA";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+FRA";
   set newname for datafile  3 to 
 "+FRA";
   set newname for datafile  4 to 
 "+FRA";
   set newname for datafile  5 to 
 "+FRA";
   set newname for datafile  6 to 
 "+FRA";
   set newname for datafile  7 to 
 "+FRA";
   set newname for datafile  8 to 
 "+FRA";
   set newname for datafile  9 to 
 "+FRA";
   restore
   from  nonsparse   from service 
 'cs'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +FRA in control file
renamed tempfile 2 to +FRA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-MAY-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 14-MAY-18

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=976150821 file name=+FRA/CS_DG/DATAFILE/system.342.976150731
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=976150821 file name=+FRA/CS_DG/DATAFILE/sysaux.341.976150747
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=976150821 file name=+FRA/CS_DG/DATAFILE/undotbs1.340.976150793
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=976150821 file name=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/system.347.976150795
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=976150822 file name=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/sysaux.351.976150803
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=976150822 file name=+FRA/CS_DG/DATAFILE/users.350.976150811
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=976150823 file name=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/undotbs1.348.976150813
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=976150823 file name=+FRA/CS_DG/DATAFILE/undotbs2.345.976150817
Finished Duplicate Db at 14-MAY-18
[oracle@jytest1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 01:05:35 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
cs1              MOUNTED


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+FRA/CS_DG/DATAFILE/system.342.976150731
+FRA/CS_DG/DATAFILE/sysaux.341.976150747
+FRA/CS_DG/DATAFILE/undotbs1.340.976150793
+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/system.347.976150795
+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/sysaux.351.976150803
+FRA/CS_DG/DATAFILE/users.350.976150811
+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/undotbs1.348.976150813
+FRA/CS_DG/DATAFILE/undotbs2.345.976150817

8 rows selected.

对物理备库创建备重做日志文件,查询主库的联机重做日志文件,备重做日志文件的大小应该与主库联机重做日志文件的大小相同,备重做日志文件组的数量应该比主库联机重做日志文件组多一组,计算公式为(maximum # of logfiles +1) * maximum # of threads SQL> select member from v$logfile;

MEMBER
***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***211;
+DATA/CS/ONLINELOG/group_2.277.970601985
+DATA/CS/ONLINELOG/group_1.278.970601985
+DATA/CS/ONLINELOG/group_3.285.970602759
+DATA/CS/ONLINELOG/group_4.286.970602761

SQL> select group#,thread#,bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024
***212;***212;***212;- ***212;***212;***212;- ***212;***212;***212;***212;***212;
1 1 200
2 1 200
3 2 200
4 2 200

SQL> select member from v$logfile;

MEMBER
***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***212;***211;
+FRA/CS_DG/ONLINELOG/group_2.346.976150835
+FRA/CS_DG/ONLINELOG/group_1.349.976150825
+FRA/CS_DG/ONLINELOG/group_3.344.976150843
+FRA/CS_DG/ONLINELOG/group_4.352.976150855

SQL> select group#,thread#,bytes/1024/1024 from v$log;

GROUP# THREAD# BYTES/1024/1024
***212;***212;***212;- ***212;***212;***212;- ***212;***212;***212;***212;***212;
1 1 200
2 1 200
3 2 200
4 2 200

SQL> alter database add standby logfile thread 1 group 5(***216;+FRA/CS_DG/ONLINELOG/redo05.log***217;) size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6(***216;+FRA/CS_DG/ONLINELOG/redo06.log***217;) size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7(***216;+FRA/CS_DG/ONLINELOG/redo07.log***217;) size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 8(***216;+FRA/CS_DG/ONLINELOG/redo08.log***217;) size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 9(***216;+FRA/CS_DG/ONLINELOG/redo09.log***217;) size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 10(***216;+FRA/CS_DG/ONLINELOG/redo10.log***217;) size 200M;

Database altered.

设置主库相关初始化参数

log_archive_config='DG_CONFIG=(cs,cs_dg)'
log_archive_dest_1='LOCATION=+data/arch/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=cs'
log_archive_dest_2='SERVICE=cs_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=cs_dg'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
remote_login_passwordfile=exclusive
log_archive_format= %t_%s_%r.dbf
log_archive_max_processes=30

主库以备库角色运行时需要额外设置的参数。这些参数当主库被转换为备库角色运行时生效:

fal_server='cs_dg'
fal_client='cs'
db_file_name_convert='+FRA/CS_DG/', '+DATA/CS/'
log_file_name_convert='+FRA/CS_DG/', '+DATA/CS/'
standby_file_management='auto'
SQL> alter system set log_archive_dest_1='LOCATION=+data/arch/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=cs' scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=cs_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=cs_dg' scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_state_1=enable scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';

System altered.

SQL> alter system set log_archive_max_processes=30 scope=both sid='*';

System altered.

SQL> alter system set log_archive_config='DG_CONFIG=(cs,cs_dg)' scope=both sid='*';

System altered.

SQL> alter system set fal_server='cs_dg' scope=both sid='*';

System altered.

SQL> alter system set fal_client='cs' scope=both sid='*';

System altered.

SQL> alter system set db_file_name_convert='+FRA/CS_DG/', '+DATA/CS/' scope=spfile  sid='*';

System altered.

SQL> alter system set log_file_name_convert='+FRA/CS_DG/', '+DATA/CS/' scope=spfile sid='*';

System altered.

SQL> alter system set standby_file_management='auto' scope=both sid='*';

System altered.

给主库创建备重做日志文件,备重做日志文件组比联机重做日志文件多一组,主库是RAC数据库,在创建备重做日志文件时需要指定thread号:

SQL> alter database add standby logfile thread 1 group 5('+DATA/CS/ONLINELOG/redo05.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6('+DATA/CS/ONLINELOG/redo06.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7('+DATA/CS/ONLINELOG/redo07.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 8('+DATA/CS/ONLINELOG/redo08.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 9('+DATA/CS/ONLINELOG/redo09.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 2 group 10('+DATA/CS/ONLINELOG/redo10.log') size 200M;

Database altered.

Oracle用户使用srvctl在备库的另一节点注册备库和备库实例

[oracle@jytest1 dbs]$ srvctl add database -db cs_dg -oraclehome /u01/app/oracle/product/12.2.0/db/ -dbtype RAC -spfile +FRA/CS_DG/PARAMETERFILE/spfilecs.ora -pwfile +FRA/CS_DG/PASSWORD/pwdcs  -role physical_standby -startoption open -stopoption immediate -dbname cs -policy automatic -diskgroup data,fra,test

[oracle@jytest1 dbs]$ srvctl config database -db cs_dg
Database unique name: cs_dg
Database name: cs
Oracle home: /u01/app/oracle/product/12.2.0/db/
Oracle user: oracle
Spfile: +FRA/CS_DG/PARAMETERFILE/spfilecs.ora
Password file: +FRA/CS_DG/PASSWORD/pwdcs
Domain: 
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA,FRA,TEST
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oper
Database instances: 
Configured nodes: 
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed

添加实例

[oracle@jytest1 dbs]$ srvctl add instance -db cs_dg -instance cs1 -node jytest1
[oracle@jytest1 dbs]$ srvctl add instance -db cs_dg -instance cs2 -node jytest2
[oracle@jytest1 dbs]$ srvctl config database -db cs_dg
Database unique name: cs_dg
Database name: cs
Oracle home: /u01/app/oracle/product/12.2.0/db/
Oracle user: oracle
Spfile: +FRA/CS_DG/PARAMETERFILE/spfilecs.ora
Password file: +FRA/CS_DG/PASSWORD/pwdcs
Domain: 
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA,FRA,TEST
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oper
Database instances: cs1,cs2
Configured nodes: jytest1,jytest2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed

[grid@jytest1 ~]$ srvctl start database -db cs_dg

[grid@jytest1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.CRS.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.FRA.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.TEST.dg
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.chad
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.net1.network
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.ons
               ONLINE  ONLINE       jytest1                  STABLE
               ONLINE  ONLINE       jytest2                  STABLE
ora.proxy_advm
               OFFLINE OFFLINE      jytest1                  STABLE
               OFFLINE OFFLINE      jytest2                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       jytest1                  169.254.123.145 88.8
                                                             8.88.1,STABLE
ora.asm
      1        ONLINE  ONLINE       jytest1                  Started,STABLE
      2        ONLINE  ONLINE       jytest2                  Started,STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.cs_dg.db
      1        ONLINE  ONLINE       jytest1                  Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/12.2.0/db/,STABLE
      2        ONLINE  ONLINE       jytest2                  Open,Readonly,HOME=/
                                                             u01/app/oracle/produ
                                                             ct/12.2.0/db/,STABLE
ora.cvu
      1        ONLINE  ONLINE       jytest1                  STABLE

ora.jy.jy_srv.svc
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest1.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.jytest2.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       jytest1                  Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       jytest2                  STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       jytest1                  STABLE
--------------------------------------------------------------------------------
[oracle@jytest1 ~]$ export ORACLE_SID=cs1
[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 23:02:41 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set line 120
SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
CS        PHYSICAL STANDBY READ ONLY
CS        PHYSICAL STANDBY READ ONLY

[oracle@jytest2 dbs]$ export ORACLE_SID=cs2
[oracle@jytest2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 23:02:47 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set line 120
SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
CS        PHYSICAL STANDBY READ ONLY
CS        PHYSICAL STANDBY READ ONLY

在备库的单个节点上执行实时重做应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

alert日志信息如下

Media Recovery Log +TEST/arch/cs_dg/2_129_970601983.dbf
2018-05-15T23:04:44.911711+08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
2018-05-15T23:04:45.116759+08:00
Media Recovery Log +TEST/arch/cs_dg/1_145_970601983.dbf
2018-05-15T23:04:45.676517+08:00
Media Recovery Log +TEST/arch/cs_dg/2_130_970601983.dbf
2018-05-15T23:05:31.642093+08:00
Media Recovery Log +TEST/arch/cs_dg/2_131_970601983.dbf

在主库创建表空间test

SQL> create tablespace test;

Tablespace created.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
TEST

7 rows selected.


SQL> select con_id,ts#,name from v$tablespace;

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         1          1 SYSAUX
         1          0 SYSTEM
         1          2 UNDOTBS1
         1          4 USERS
         1          3 TEMP
         2          0 SYSTEM
         2          1 SYSAUX
         2          2 UNDOTBS1
         2          3 TEMP
         1          5 UNDOTBS2
         1          6 TEST

SQL> select ts#,name from v$datafile where ts#=6 and con_id=1;

       TS#  NAME
    ----------  --------------------------------------------------------------------------------
         6  +DATA/CS/DATAFILE/test.326.976211663

[oracle@jytest1 trace]$ tail -f alert_cs1.log 
2018-05-15T23:04:44.736977+08:00
NOTE: dependency between database cs_dg and diskgroup resource ora.TEST.dg is established
2018-05-15T23:04:44.842580+08:00
Media Recovery Log +TEST/arch/cs_dg/2_129_970601983.dbf
2018-05-15T23:04:44.911711+08:00
Completed: alter database recover managed standby database using current logfile disconnect from session
2018-05-15T23:04:45.116759+08:00
Media Recovery Log +TEST/arch/cs_dg/1_145_970601983.dbf
2018-05-15T23:04:45.676517+08:00
Media Recovery Log +TEST/arch/cs_dg/2_130_970601983.dbf
2018-05-15T23:05:31.642093+08:00
Media Recovery Log +TEST/arch/cs_dg/2_131_970601983.dbf
2018-05-15T23:06:11.885256+08:00
Media Recovery Log +TEST/arch/cs_dg/1_146_970601983.dbf
2018-05-15T23:06:26.490187+08:00
Media Recovery Log +TEST/arch/cs_dg/2_132_970601983.dbf
2018-05-15T23:06:36.761337+08:00
Media Recovery Log +TEST/arch/cs_dg/2_133_970601983.dbf
2018-05-15T23:06:38.014959+08:00
Media Recovery Log +TEST/arch/cs_dg/1_147_970601983.dbf
2018-05-15T23:06:38.932380+08:00
Media Recovery Log +TEST/arch/cs_dg/1_148_970601983.dbf
2018-05-15T23:06:40.372178+08:00
Media Recovery Log +TEST/arch/cs_dg/2_134_970601983.dbf
2018-05-15T23:06:40.994801+08:00
Media Recovery Log +TEST/arch/cs_dg/1_149_970601983.dbf
2018-05-15T23:06:41.656032+08:00
Media Recovery Log +TEST/arch/cs_dg/2_135_970601983.dbf
2018-05-15T23:06:47.456319+08:00
Media Recovery Waiting for thread 2 sequence 136 (in transit)
2018-05-15T23:06:47.474190+08:00
Recovery of Online Redo Log: Thread 2 Group 9 Seq 136 Reading mem 0
  Mem# 0: +FRA/CS_DG/ONLINELOG/redo09.log
2018-05-15T23:06:52.286510+08:00
Media Recovery Waiting for thread 1 sequence 150 (in transit)
2018-05-15T23:06:52.338688+08:00
Recovery of Online Redo Log: Thread 1 Group 6 Seq 150 Reading mem 0
  Mem# 0: +FRA/CS_DG/ONLINELOG/redo06.log
2018-05-15T23:08:48.743309+08:00
Successfully added datafile 10 to media recovery
Datafile #10: '+FRA/CS_DG/DATAFILE/test.327.976230527'

从上面信息可以看到在备库创建的表空间test的数据文件名,下面从备库进行查询来验证

SQL>  select con_id,ts#,name from v$tablespace;

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         1          1 SYSAUX
         1          0 SYSTEM
         1          2 UNDOTBS1
         1          4 USERS
         1          3 TEMP
         2          0 SYSTEM
         2          1 SYSAUX
         2          2 UNDOTBS1
         2          3 TEMP
         1          5 UNDOTBS2
         1          6 TEST

11 rows selected.
SQL> select ts#,name from v$datafile where ts#=6 and con_id=1;

       TS#  NAME
----------  ------------------------------------------------------------------------------------------------------------------------
         6  +FRA/CS_DG/DATAFILE/test.327.976230527

验证主库

SQL> create user c##test identified by "test" default tablespace users temporary tablespace temp;

User created.

SQL> grant dba,connect,resource to c##test;

Grant succeeded.

SQL> create table c##test.t1 as select * from dba_objects where 1=2;

Table created.

备库

SQL> desc c##test.t1
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 OWNER                                                                      VARCHAR2(128)
 OBJECT_NAME                                                                VARCHAR2(128)
 SUBOBJECT_NAME                                                             VARCHAR2(128)
 OBJECT_ID                                                                  NUMBER
 DATA_OBJECT_ID                                                             NUMBER
 OBJECT_TYPE                                                                VARCHAR2(23)
 CREATED                                                                    DATE
 LAST_DDL_TIME                                                              DATE
 TIMESTAMP                                                                  VARCHAR2(19)
 STATUS                                                                     VARCHAR2(7)
 TEMPORARY                                                                  VARCHAR2(1)
 GENERATED                                                                  VARCHAR2(1)
 SECONDARY                                                                  VARCHAR2(1)
 NAMESPACE                                                                  NUMBER
 EDITION_NAME                                                               VARCHAR2(128)
 SHARING                                                                    VARCHAR2(18)
 EDITIONABLE                                                                VARCHAR2(1)
 ORACLE_MAINTAINED                                                          VARCHAR2(1)
 APPLICATION                                                                VARCHAR2(1)
 DEFAULT_COLLATION                                                          VARCHAR2(100)
 DUPLICATED                                                                 VARCHAR2(1)
 SHARDED                                                                    VARCHAR2(1)
 CREATED_APPID                                                              NUMBER
 CREATED_VSNID                                                              NUMBER
 MODIFIED_APPID                                                             NUMBER
 MODIFIED_VSNID                                                             NUMBER

主库:节点1

SQL> insert into c##test.t1 select * from dba_objects;

73390 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from c##test.t1;

  COUNT(*)
----------
     73390

备库:节点1

SQL> select count(*) from c##test.t1;

  COUNT(*)
----------
     73390

主库:节点2

SQL> insert into c##test.t1 select * from c##test.t1;

73390 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from c##test.t1;

  COUNT(*)
----------
    146780

备库: 节点2

SQL> select count(*) from c##test.t1;

  COUNT(*)
----------
    146780

主库:

SQL> truncate table c##test.t1;

Table truncated.

SQL> select count(*) from c##test.t1;

  COUNT(*)
----------
         0

备库:

SQL> select count(*) from c##test.t1;

  COUNT(*)
----------
         0

验证物理备库是否执行正确
在创建物理备库后并且设置重做传输服务,可能想要验证主库的数据库修改是否会成功的传输到备库。对于备库可以查询v$managed_standby视图来验证重做是否被从主库传输到备库并应用。

[oracle@jytest1 ~]$ export ORACLE_SID=cs1
[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 16 22:41:10 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select client_process,process,thread#,sequence#,status from v$managed_standby where client_process='LGWR' or process='MRP0';

CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR     RFS                1        151 IDLE
N/A      MRP0               2        139 APPLYING_LOG

上面的查询对于使用CLIENT_PROCESS为LGWR的主库会显示一行记录,它指示重做传输工作正常并且主重做线程将会被发送到备库。 如果主库是RAC数据库,那么对于使用CLIENT_PROCESS为LGWR的当前活动的每个主库实例都会显示一行记录。上面的查询对于MRP也行显示一行。如果MRP的状态显示为APPLYING_LOG并且SEQUENCE#等于主库当前正被发送的日志
序列号,那么备库已经解决了所有的日志差异并且当前处于实时应用日志模式。

查询主库当前正被发送日志的序列号为139与上面的MRP进程所显示的sequence#(139)相同

SQL> select group#,thread#,sequence#,status from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1        151 CURRENT
         2          1        150 INACTIVE
         3          2        139 CURRENT
         4          2        138 INACTIVE

注意MRP进程可能显示的sequence#比主库当前被发送的日志序列号小,那么这就表示正在应用的归档重做日志文件与发送的日志文件之间存在差异并且它并没有赶上。一旦所有差异被解决,相同的查询将显示MRP正在应用当前sequence#。

Oraclc 12C使用不一致备份执行跨平台传输表空间

使用不一致备份执行跨平台传输表空间
RMAN可以跨平台传输不一致表空间备份。不一致表空间备份是当表空间处于读写状态时对一个或多个表空间创建的备份。不一致是指备份的数据文件包含了检查点之后发生的改变。跨平台不一致备份所生成的外部数据文件不能 直接附加到目标数据库。可以通过应用跨平台增量备份来使外部数据文件达到一致。这种备份必须包含附加传输表空间到目标数据库所需要元数据的dump文件。

不一致备份能让你减少停机时间。当表空间联机时,在源数据库上创建跨平台不一致备份。第一次备份必须是level 0增量备份,后续,创建level 1增量备份它包含从最近level 1备份后所有发生的改变。就算其它level 1增量 备份正在源数据库上创建时,这些level 0与level 1增量备份可以被还原与应用到目标数据库。在开始应用之前所创建的level 1备份到目标数据库之前不需要等待所有level 1增量备份在源数据库上创建完成就可以执行。因为 当创建增量备份时这些表空间仍然处于联机状态,在这时应用程序仍然可以正常运行。当最后一次创建增量备份时需要将表空间置于只读状态,这时应用程序处于停机状态。最后的备份必须包含附加表空间到目标数据库所需要 的元数据。

在目标数据库上,首先还原level 0增量备份来创建外部数据文件集。下一步就是应用当表空间处于读写状态时所生成的level 1增量备份至这些还原的外部数据文件。以创建这些备份的顺序来应用这些备份。在大多数情况下, 目标数据库的内容就赶上最后增量备份之前的最近一次level 1增量备份所包含的内容,创建最后增量备份时需要将源数据库中的表空间设置为只读状态。最后还原最终的level 1增量备份时会使用外部数据文件达到一致状态。 这次的备份包含附加表空间到目标数据库中所需要的元数据。

可以使用备份集或镜像副本来跨平台传输不一致备份,使用backup命令来以备份集方式来创建跨平台备份。使用convert命令来以镜像副本来创建跨平台备份。

在源数据为上创建不一致与增量备份
在backup或convert命令中使用allow inconsistent子句来为表空间创建跨平台不一致备份。当创建不一致备份时这些表空间处于读写状态。为了创建增量备份,在backup命令中使用incremental level 1子句。

第一次不一致备份是level 0增量备份。后续可以创建多个跨平台的level 1增量备份。最后一次跨平台增量备份必须是一个一致的备份因此在创建时表空间必须处于只读状态。在创建最后一次增量备份时,在backup命令中使用 dump file子句来创建包含表空间元数据的dump文件。

当使用convert命令时,必须显式的使用Data Pump导出工具来创建包含表空间元数据的导出dump文件。allo inconsistent子句不能用于跨平台数据库备份。

在目标数据库还原与恢复不一致备份
当源数据库中表空间处于读写状态时首先在目标数据库中还原跨平台level 0增量备份。这个操作会还原备份与创建外部数据文件副本。因为创建备份时这些表空间不是处于只读状态所以这些外部数据文件处于一不致状态。为了 使用这些外部数据文件达到一致并且达到一致检查点SCN,以创建增量备份的顺序来应用这些增量备份。最后应用的增量备份在创建时源数据库中的表空间必须处于只读状态。下一步就是将表空间附加到目标数据库中,在传输表 空间时可以还原与导入包含元数据的dump文件。

对还原的数据文件应用跨平台增量备份所要满足的条件
为了对还原的外部数据文件集成功应用跨平台增量表空间备份必须满足以下条件:
.每个被包含在跨平台增量备份中的数据文件,它的开始SCN号必须小于外部数据文件副本的当前检查点SCN号。

.对于通过还原操作所创建的外部数据文件副本必须不能被修改。例如,如果外部数据文件副本已经被附加到目标数据库中,并且处于读写状态,然后置于只读状态,那么RMAN会认为这些文件已经被修改。

跨平台传输不一致表空间
跨平台传输不一致表空间分主以下三个主要操作
1.在源数据库上创建传输表空间所需要的文件。
2.将文件从源主机传输到目标主机上。
3.在目标主机上还原表空间并附加到目标数据库

一在源数据库上创建传输表空间所需要的文件
1.1如果被传输的表空间是TDE加密表空间,那么在创建备份集之前指定加密密码
RMAN> SET PASSPHRASE ON IDENTIFIED BY encr_temp;

1.2对于要被传输的表空间在其处于读写状态下创建跨平台level 0不一致备份。在backup命令中使用allow inconsistent and incremental level 0子句来指示备份是不一致备份。

1.3在要被传输的表空间在其处于读写状态下创建跨平台level 1增量备份。在level 0不一致备份后,在表空间处于读写状态下时可以创建任何数量的level 1增量备份。在backup命令中使用allow inconsistent与 incremental level 1子句来创建增量备份。当被传输表空间处于读写状态时频繁的执行增量备份的好处就是可以减少当表空间处于只读状态时所创建的最后增量备份应用到目标数据库的时间。

1.4当被传输表空间处于只读状态时创建跨平台level 1增量备份。这是最后一次增量备份并且它必须包含附加表空间到目标数据库中所需要的表空间元数据。在backup命令中使用incremental level 1子句来创建level 1增量 备份。

当使用backup命令执地跨平台传输时,使用datapump子句在创建增量备份的同时也生成导出dump文件。dump文件被创建在一个单独的备份集中。当使用镜像副本来创建跨平台增量备份时,必须显式地使用Data Pump导出工具来 创建包含表空间元数据的dump文件。

二将文件从源主机传输到目标主机上
使用ftp,操作系统复制命令或一些其它机制来移动备份集,数据文件与dump文件到目标主机上。

三在目标主机上还原表空间并附加到目标数据库
3.1如果被传输表空间是TDE加密表空间,那么通过下面的方式来指定密码
RMAN> SET PASSPHRASE ON IDENTIFIED BY encr_temp;

3.2还原跨平台level 0不一致备份
还原操作将会在目标数据库上创建一组外部数据文件。这些外部数据文件是不一致状态,并且它们在附加到目标数据库之前需要执行恢复。使用restore命令来还原跨平台level 0不一致备份。当还原跨平台不一致备份,它是由 备份集组成,使用from backupset子句来指定包含level 0不一致备份的备份集名称。

3.3应用表空间处于读写状态时所创建的跨平台level 1增量备份到还原的外部数据文件。
如果创建了多次跨平台level 1增量备份,那么这些增量备份必须以相同的顺序来进行应用。使用recover命令来应用增量备份。recover命令中的foreign datafilecopy子句必须列出增量备份所要应用到的每个数据文件。使用 from backupset子句来指定包含被恢复数据的备份集名称。

3.4应用表空间处于只读状态时所创建的跨平台level 1增理备份到还原的外部数据文件。
使用recover命令来应用增量备份。recover命令中的foreign datafilecopy子句必须列出增量备份所要应用到的每个数据文件。使用from backupset子句来指定包含被恢复数据的备份集名称。

3.5还原包含表空间元数据的备份集
使用restore命令来还原在创建跨平台增量备份时一同创建的包含dump文件的备份集。这种备份是当表空间处于只读状态时所创建的。可以使用dump file子句来指定目标数据库上dump文件的名称,datapump destination子句 来指定存储dump文件的目录。如果这些子句被忽略,RMAN将使用配置的缺省值。当使用备份集传输数据时,使用from backupset子句来指定包含dump文件的备份集。

3.6向目标数据库导入包含表空间元数据的dump文件
通过使用Data Pump导入工具来导入在执行增量备份时所生成的dump文件来附加被恢复的表空间到目标数据库中。运行Data Pump导入工具的用户必须有sysdba权限。

Performing Cross-Platform Inconsistent Tablespace Transport Using Backup Sets
下面的例子将Linux平台上的RAC CDB中的jypdb的表空间tts与cs传输到windows平台上的单实例CDB的jypdb数据库中。使用不一致备份集对表空间执行跨平台传输的操作如下:
1.检查源数据库与目标数据库的compatible参数是否设置为12.0.0或更高版本
源数据库

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
noncdb_compatible                    boolean     FALSE

目标数据库

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
noncdb_compatible                    boolean     FALSE

2.对源数据库中的要被传输的表空间tts与cs执行自包含检查

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.检查源平台与目标平台的字节序
源平台

SQL> select d.platform_name,endian_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

目标平台

SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d  where t

PLATFORM_NAME                                                                       ENDIAN_FORMAT
--------------------------------------------------------------------------------    --------------
Microsoft Windows x86 64-bit                                                        Little

4.当表空间jypdb:tts,与jypdb:cs处于读写状态时创建跨平台level 0不一致备份。备份集名为tts_cs_incon.bck并存储在/ora_backup/tts目录中

RMAN> backup for transport allow inconsistent incremental level 0 tablespace JYPDB:'TTS',JYPDB:'CS' format '/ora_backup/tts/tts_cs_incon.bck';

Starting backup at 09-FEB-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 instance=jy1 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 09-FEB-18
channel ORA_DISK_1: finished piece 1 at 09-FEB-18
piece handle=/ora_backup/tts/tts_cs_incon.bck tag=TAG20180209T184928 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-FEB-18

因为使用for transport代替to platform子句,因此这种跨平台备份可以被还原到任何平台。这种转换操作将在目标数据库上完成。

5.对tts.t1与cs.t2表进行更新

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        45


SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        46

SQL> insert into tts.t1 select * from tts.t1;

45 rows created.

SQL> insert into cs.t2 select * from cs.t2;

46 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        90

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        92

6.当表空间jypdb:tts,jypdb:cs处于读写状态时创建跨平台的level 1增量备份。备份集名为tts_cs_incon1.bck并存储在/ora_backup/tts目录中

RMAN> backup for transport allow inconsistent incremental level 1 tablespace JYPDB:'TTS',JYPDB:'CS' format '/ora_backup/tts/tts_cs_incon1.bck';

Starting backup at 09-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 09-FEB-18
channel ORA_DISK_1: finished piece 1 at 09-FEB-18
piece handle=/ora_backup/tts/tts_cs_incon1.bck tag=TAG20180209T185930 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-FEB-18

为了最小化应用程序停机的时间,在步骤4,6所创建的level 0与level 1增量备份当源数据库中表空间处于读写状态的情况下,可以被还原与应用到目标数据库。当目标数据库的内容赶上最近一次增量备份的内容时,可以将源 数据库中的表空间设置为只读状态来创建最终的增量备份。

7.将源数据库中的表空间jypdb:tts,jypdb:cs设置只读模式

SQL> alter tablespace tts read only;

Tablespace altered.

SQL> alter tablespace cs read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TEST                           ONLINE
TTS                            READ ONLY
CS                             READ ONLY

9 rows selected.

8.创建最终的跨平台level 1增量备份。这次备份包含上一次备份以来所有的改变并且包含导出dump文件它包含表空间元数据。

RMAN> backup for transport  incremental level 1 tablespace JYPDB:'TTS',JYPDB:'CS' format '/ora_backup/tts/tts_cs_incr.bck' DATAPUMP FORMAT '/ora_backup/tts/tts_cs_incr_dp.bck';

Starting backup at 09-FEB-18
starting full resync of recovery catalog
full resync complete
using channel ORA_DISK_1
Running TRANSPORT_SET_CHECK on specified tablespaces
TRANSPORT_SET_CHECK completed successfully

Performing export of metadata for specified tablespaces...
   EXPDP> Starting "SYS"."TRANSPORT_EXP_JY_vqqz":
   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_vqqz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TRANSPORT_EXP_JY_vqqz is:
   EXPDP>   /u01/app/oracle/product/12.2.0/db/dbs/backup_tts_JY_71118.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_vqqz" successfully completed at Fri Feb 9 19:19:28 2018 elapsed 0 00:09:02
Export completed

channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 09-FEB-18
channel ORA_DISK_1: finished piece 1 at 09-FEB-18
piece handle=/ora_backup/tts/tts_cs_incr.bck tag=TAG20180209T190934 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backup set
input Data Pump dump file=/u01/app/oracle/product/12.2.0/db/dbs/backup_tts_JY_71118.dmp
channel ORA_DISK_1: starting piece 1 at 09-FEB-18
channel ORA_DISK_1: finished piece 1 at 09-FEB-18
piece handle=/ora_backup/tts/tts_cs_incr_dp.bck tag=TAG20180209T190934 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-FEB-18
starting full resync of recovery catalog
full resync complete

9.将所有生成的备份集与Data Pump导出的dump文件传输到目标平台的D:\app\oracle\oradata\tts目录中

10.还原步骤4生成的跨平台level 0不一致备份
使用foreign datafile子句来指定被还原的数据文件。from platform子句指定创建备份的平台名称。在目标数据库中转换备份需要指定这个子句。在下面的例子中数据文件号为22,23的数据文件会被还原,并且通过指定 format子句来为数据文件指定名称。

RMAN> RESTORE FROM PLATFORM 'Linux x86 64-bit'
2> FOREIGN DATAFILE 22 FORMAT 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\tts_22.df',
3> 23
4> FORMAT 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\cs_23.df'
5> FROM BACKUPSET 'D:\app\oracle\oradata\tts\tts_cs_incon.bck';

从位于 09-2月 -18 的 restore 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=2652 设备类型=DISK

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原外部文件 00022
通道 ORA_DISK_1: 正在还原外部文件 00023
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tts\tts_cs_incon.bck
通道 ORA_DISK_1: 将外部文件 22 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\TTS_22.DF
通道 ORA_DISK_1: 将外部文件 23 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\CS_23.DF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tts\tts_cs_incon.bck
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:04
在 09-2月 -18 完成了 restore

11.通过应用步骤6所创建的level 1增量备份来恢复步骤10中还原的外部数据文件

RMAN> RECOVER
2> FROM PLATFORM 'Linux x86 64-bit'
3> FOREIGN DATAFILECOPY 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\tts_22.df','D:\APP\ORACLE\ORADATA\JY\DATAFILE\cs_23.df'
4> FROM BACKUPSET 'D:\app\oracle\oradata\tts\tts_cs_incon1.bck';

从位于 09-2月 -18 的 restore 开始
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原外部文件 D:\APP\ORACLE\ORADATA\JY\DATAFILE\tts_22.df
通道 ORA_DISK_1: 正在还原外部文件 D:\APP\ORACLE\ORADATA\JY\DATAFILE\cs_23.df
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tts\tts_cs_incon1.bck
通道 ORA_DISK_1: 外部片段句柄 = D:\APP\ORACLE\ORADATA\TTS\TTS_CS_INCON1.BCK
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
在 09-2月 -18 完成了 restore

12.使用最终的level 1增量备份来恢复步骤10中还原的外部数据文件

RMAN> RECOVER
2> FROM PLATFORM 'Linux x86 64-bit'
3> FOREIGN DATAFILECOPY 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\tts_22.df','D:\APP\ORACLE\ORADATA\JY\DATAFILE\cs_23.df'
4> FROM BACKUPSET 'D:\app\oracle\oradata\tts\tts_cs_incr.bck';

从位于 09-2月 -18 的 restore 开始
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原外部文件 D:\APP\ORACLE\ORADATA\JY\DATAFILE\tts_22.df
通道 ORA_DISK_1: 正在还原外部文件 D:\APP\ORACLE\ORADATA\JY\DATAFILE\cs_23.df
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tts\tts_cs_incr.bck
通道 ORA_DISK_1: 外部片段句柄 = D:\APP\ORACLE\ORADATA\TTS\TTS_CS_INCR.BCK
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:01
在 09-2月 -18 完成了 restore

13.还原包含导出dump文件的备份集,dump文件是附加表空间到目标数据库中所需要的。

RMAN> RESTORE
2> FROM PLATFORM 'Linux x86 64-bit'
3> DUMP FILE 'tts_cs_restore_md.dmp'
4> DATAPUMP DESTINATION 'D:\app\oracle\oradata\tts\'
5> FROM BACKUPSET 'D:\app\oracle\oradata\tts\tts_cs_incr_dp.bck';

从位于 09-2月 -18 的 restore 开始
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在将数据泵转储文件还原到 D:\app\oracle\oradata\tts\/tts_cs_restore_md.dmp
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tts\tts_cs_incr_dp.bck
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tts\tts_cs_incr_dp.bck
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:02
在 09-2月 -18 完成了 restore

14.附加表空间到目标数据库中

SQL> CREATE OR REPLACE DIRECTORY dp_dir  AS 'D:\app\oracle\oradata\tts\';

目录已创建。


SQL> GRANT read,write ON DIRECTORY dp_dir TO public;

授权成功。


C:\Users\Administrator>impdp system/abcd@jypdb directory=dp_dir dumpfile=tts_cs_restore_md.dmp transport_datafiles='D:\APP\ORACLE\ORADATA\JY\DATAFILE\tts_22.df','D:\APP\ORACLE \ORADATA\JY\DATAFILE\cs_23.df' nologfile=Y

Import: Release 12.2.0.1.0 - Production on 星期五 2月 9 19:56:16 2018

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

连接到: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@jypdb directory=dp_dir dumpfile=tts_cs_restore_md.dmp transport_datafiles='D:\APP\ORACLE\ORADATA\JY\DATAFILE\tts_22.df','D: \APP\ORACLE\ORADATA\JY\DATAFILE\cs_23.df' nologfile=Y
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/STATISTICS/MARKER
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 星期五 2月 9 19:56:49 2018 elapsed 0 00:00:29 成功完成

15.检查tts.t1与cs.t2表的数据来验证传输是否成功

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TEST                           ONLINE
TTS                            READ ONLY
CS                             READ ONLY

已选择 9 行。

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        90

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        92

SQL> alter tablespace tts read write;

表空间已更改。

SQL> alter tablespace cs read write;

表空间已更改。

到此使用不一致跨平台备份传输表空间操作完成

Oracle 12C Transport a Database to a Different Platform Using Backup Sets

可以从源平台传输整个数据库到有相同字节序的不同平台。当为了传输数据库而创建跨平台备份时,可以在源平台或目标平台上执行对数据库的转换。在目标平台执行对数据库的转换的最大好处就是执行转换的开锁从源平台转 移到了目标平台。
使用备份集执行跨平台数据库传输的先决条件
在为了跨平台传输数据库而创建跨平台备份之前需要满足以下条件:
.源数据库与目标数据库的spfile文件中的compatible参数必须设置为12.0.0或更高的版本。
.源数据库必须是read-only模式打开。
.dbms_tdb.check_db过程必须成功运行。
.源平台与目标平台必须有相同的字节序。

下面的例子将使用备份集执行跨平台传输数据库,源平台为Linux 64位,目标平台为windows 64位其具体操作如下
1.在目标平台上创建数据库实例jy
设置环境变量ORACLE_SID

C:\Users\Administrator>set ORACLE_SID=jy

创建实例

C:\Users\Administrator>oradim -new -sid jy -startmode m

输入 Oracle 服务用户的口令:
实例已创建。

创建Oracle口令文件

C:\Users\Administrator>orapwd file=D:\app\oracle\product\12.2.0\dbhome_1\database\pwdjy.ora password=xxzx$7817600  sysbackup=xxzx$7817600

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = jy)
(ORACLE_HOME =D:\app\oracle\product\12.2.0\dbhome_1)
(GLOBAL_DBNAME=jy)
)
)

在tnsnames.ora文件中增加以下内容

JY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-ROUOJ6ERFO3)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jy)
      (UR = A)
    )
  )

2.利用源数据库的spfile文件来为目标数据库创建spfile文件

SQL> create pfile='$ORACLE_HOME/dbs/inityyl.ora' from spfile;

File created.

将上面生成的inityyl.ora文件复制到目标平台D:\app\oracle\product\12.2.0\dbhome_1\database目录下并将其内容修改成如下所示

*.audit_file_dest='D:\app\oracle\admin\jy\adump'
*.cluster_database=false
*.compatible='12.2.0'
*.control_files='D:\APP\ORACLE\ORADATA\JY\CONTROLFILE\control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='D:\app\oracle\oradata\'
*.db_name='jy'
*.diagnostic_dest='D:\app\oracle\'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jyXDB)'
*.enable_pluggable_database=true
jy2.instance_number=2
jy1.instance_number=1
*.log_archive_dest_1='location=D:\app\oracle\oradata\arch\jy'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1g
*.processes=2000
*.remote_login_passwordfile='exclusive'
*.sga_max_size=2147483648
*.sga_target=2147483648
jy2.thread=2
jy1.thread=1
*.undo_retention=8640
jy1.undo_tablespace='UNDOTBS1'
jy2.undo_tablespace='UNDOTBS2'

3.在目标平台上启动实例jy

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期四 2月 1 01:23:54 2018

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

已连接到空闲例程。


SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 2147483648 bytes
Fixed Size                  8920984 bytes
Variable Size            1392509032 bytes
Database Buffers          738197504 bytes
Redo Buffers                7856128 bytes

创建spfile参数文件并重启实例到nomount状态

SQL> create spfile from pfile='D:\app\oracle\product\12.2.0\dbhome_1\database\initjy.ora';

文件已创建。

SQL> shutdown immediate
ORA-01507: ??????


ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 2147483648 bytes
Fixed Size                  8920984 bytes
Variable Size            1392509032 bytes
Database Buffers          738197504 bytes
Redo Buffers                7856128 bytes
SQL> show parameter spfile

NAME                                 TYPE                           VALUE
------------------------------------ ----------------------         -----------------------------------------------------------
spfile                               string                         D:\APP\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\SPFILEJY.ORA

测试tns连接实例jy是否能够成功

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on 星期四 2月 1 18:01:52 2018

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

SQL> conn sys/abcd@jy as sysdba
已连接。

4.使用有sysdba权限的用户通过SQL*Plus连接到源数据库,并查询v$transportable_platform来查询目标平台的名字,因为传输整个数据库源平台与目标平台的字节序应该相同。

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 1 18:06:37 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set long 200
SQL> set linesize 200
SQL> select platform_name,endian_format from v$transportable_platform;

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit)                                                                               Big
Solaris[tm] OE (64-bit)                                                                               Big
Microsoft Windows IA (32-bit)                                                                         Little
Linux IA (32-bit)                                                                                     Little
AIX-Based Systems (64-bit)                                                                            Big
HP-UX (64-bit)                                                                                        Big
HP Tru64 UNIX                                                                                         Little
HP-UX IA (64-bit)                                                                                     Big
Linux IA (64-bit)                                                                                     Little
HP Open VMS                                                                                           Little
Microsoft Windows IA (64-bit)                                                                         Little
IBM zSeries Based Linux                                                                               Big
Linux x86 64-bit                                                                                      Little
Apple Mac OS                                                                                          Big
Microsoft Windows x86 64-bit                                                                          Little
Solaris Operating System (x86)                                                                        Little
IBM Power Based Linux                                                                                 Big
HP IA Open VMS                                                                                        Little
Solaris Operating System (x86-64)                                                                     Little
Apple Mac OS (x86-64)                                                                                 Little

20 rows selected.

我这里源平台为Linux x86 64-bit,目标平台为Microsoft Windows IA (64-bit),两个平台的字节序都是Little

5.给RMAN备份输出文件选择命名方法
使用backup命令的format子句来为备份输出文件选择输出方法。比如使用format子句来为备份输出文件指定存储目录/ora_backup/tdb并且为每个文件生成以transport_为前缀的唯一文件名。FORMAT ***216;/ora_backup/tdb/transport_%U***217;

6.启动RMAN连接到源数据库(整个RAC CDB)并将重启源数据库并以只读方式打开。

[oracle@jytest1 tdb]$ rman target sys/abcd0@jy catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 1 18:37:04 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

RMAN> shutdown immediate

starting full resync of recovery catalog
full resync complete
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database: JY (DBID=979425723)
database is already started
starting full resync of recovery catalog
full resync complete

RMAN> alter database open read only;

starting full resync of recovery catalog
full resync complete
Statement processed

7.在backup命令中使用for transport或to platform子句来备份源数据库。下面的例子对整个数据库创建跨平台传输备份。备份可以被还原到任何与源平台有相同字节序的平台。这里源平台为Linux x86 64-bit,因为使用了 for transport子句,那么对整个数据库的转换操作将在目标平台上执行。跨平台数据库备份被存储在/ora_backup/tdb目录中。

RMAN> backup for transport format '/ora_backup/tdb/db_transport_%U' database;

Starting backup at 01-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/JY/DATAFILE/sysaux.298.962209605
input datafile file number=00009 name=+DATA/JY/DATAFILE/undotbs2.312.962209605
input datafile file number=00004 name=+DATA/JY/DATAFILE/undotbs1.277.962209605
input datafile file number=00001 name=+DATA/JY/DATAFILE/system.317.962209603
input datafile file number=00007 name=+DATA/JY/DATAFILE/users.301.962209605
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7ssq5l1i_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
input datafile file number=00011 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
input datafile file number=00013 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
input datafile file number=00010 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
input datafile file number=00012 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
input datafile file number=00015 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7tsq5l5g_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
input datafile file number=00016 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
input datafile file number=00018 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
input datafile file number=00019 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
input datafile file number=00021 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409
input datafile file number=00020 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7usq5l8p_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
input datafile file number=00005 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
input datafile file number=00008 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
channel ORA_DISK_1: starting piece 1 at 01-FEB-18
channel ORA_DISK_1: finished piece 1 at 01-FEB-18
piece handle=/ora_backup/tdb/db_transport_7vsq5lbf_1_1 tag=TAG20180201T192745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 01-FEB-18



[oracle@jytest1 tdb]$ ls -lrt
total 5532808
-rw-r----- 1 oracle asmadmin 3146178560 Feb  1 13:27 db_transport_7ssq5l1i_1_1
-rw-r----- 1 oracle asmadmin  990789632 Feb  1 13:29 db_transport_7tsq5l5g_1_1
-rw-r----- 1 oracle asmadmin  988348416 Feb  1 13:30 db_transport_7usq5l8p_1_1
-rw-r----- 1 oracle asmadmin  540270592 Feb  1 13:31 db_transport_7vsq5lbf_1_1

备份源数据库的控制文件到跨踪文件中用于在目标平台为目标数据库重新创建控制文件

SQL> alter session set tracefile_identifier='create';

Session altered.

SQL> alter database backup controlfile to trace resetlogs;

Database altered.

[root@jytest1 /]# find / -name *create*.trc
/u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_22752_create.trc

[root@jytest1 /]# cat /u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_22752_create.trc
Trace file /u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_22752_create.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /u01/app/oracle/product/12.2.0/db
System name:    Linux
Node name:      jytest1.jydba.net
Release:        3.8.13-55.1.6.el7uek.x86_64
Version:        #2 SMP Wed Feb 11 14:18:22 PST 2015
Machine:        x86_64
Instance name: jy1
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 22752, image: oracle@jytest1.jydba.net (TNS V1-V3)


*** 2018-02-01T23:42:02.696976+08:00 (CDB$ROOT(1))
*** SESSION ID:(6.7558) 2018-02-01T23:42:02.697094+08:00
*** CLIENT ID:() 2018-02-01T23:42:02.697109+08:00
*** SERVICE NAME:(SYS$USERS) 2018-02-01T23:42:02.697120+08:00
*** MODULE NAME:(sqlplus@jytest1.jydba.net (TNS V1-V3)) 2018-02-01T23:42:02.697131+08:00
*** ACTION NAME:() 2018-02-01T23:42:02.697141+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2018-02-01T23:42:02.697150+08:00
*** CONTAINER ID:(1) 2018-02-01T23:42:02.697161+08:00

-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="jy"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?#/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=+TEST/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JY" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/JY/ONLINELOG/group_1.261.961976319'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '+DATA/JY/ONLINELOG/group_2.302.961976321'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/JY/DATAFILE/system.317.962209603',
  '+DATA/JY/DATAFILE/sysaux.298.962209605',
  '+DATA/JY/DATAFILE/undotbs1.277.962209605',
  '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675',
  '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675',
  '+DATA/JY/DATAFILE/users.301.962209605',
  '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675',
  '+DATA/JY/DATAFILE/undotbs2.312.962209605',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409',
  '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353',
  '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783'
CHARACTER SET ZHS16GBK
;
-- Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('+TEST/jy/snapcf_jy.f');
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''+test/jy/snapcf_jy.f''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''+test/rman_backup/%F''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+TEST/arch/1_1_934293149.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+TEST/arch/1_1_961976319.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 3 '+DATA/JY/ONLINELOG/group_3.263.961976697' SIZE 200M BLOCKSIZE 512 REUSE,
  GROUP 4 '+DATA/JY/ONLINELOG/group_4.262.961976705' SIZE 200M BLOCKSIZE 512 REUSE;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/TEMPFILE/temp.299.961976339'
     SIZE 150994944  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = PDB$SEED;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/TEMPFILE/temp.297.962209865'
     SIZE 67108864  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = JYPDB;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/TEMPFILE/temp.276.962210519' REUSE;
ALTER SESSION SET CONTAINER = TESTPDB;
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/TEMPFILE/temp.258.962469435' REUSE;
ALTER SESSION SET CONTAINER = CDB$ROOT;
-- End of tempfile additions.

8.将在源平台上使用rman backup命令生成的备份集与备份的控制文件传输到目标平台的D:\app\oracle\oradata\tdb目录中

9.使用rman连接到目标数据库并确保目标数据库(单实例CDB)处于nomount状态

C:\Users\Administrator>rman target sys/abcd@jy

恢复管理器: Release 12.2.0.1.0 - Production on 星期四 2月 1 19:55:44 2018

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

已连接到目标数据库: JY (未装载)

10.使用有foreign database子句的restore命令来还原从源平台传输过来的备份集

RMAN> restore from platform 'Linux x86 64-bit' foreign database to new from backupset 'D:\app\oracle\oradata\tdb\db_transport_7ssq5l1i_1_1' backupset 'D:\app\oracle\oradata\tdb \db_transport_7tsq5l5g_1_1' backupset 'D:\app\oracle\oradata\tdb\db_transport_7usq5l8p_1_1' backupset 'D:\app\oracle\oradata\tdb\db_transport_7vsq5lbf_1_1';

从位于 02-2月 -18 的 restore 开始
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7ssq5l1
i_1_1
通道 ORA_DISK_1: 将外部文件 3 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 9 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 4 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 1 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF
通道 ORA_DISK_1: 将外部文件 7 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7ssq5l1i_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:56
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7tsq5l5
g_1_1
通道 ORA_DISK_1: 将外部文件 14 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 11 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 13 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 10 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 12 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 15 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF
通道 ORA_DISK_1: 将外部文件 22 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF
通道 ORA_DISK_1: 将外部文件 23 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7tsq5l5g_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:26
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7usq5l8
p_1_1
通道 ORA_DISK_1: 将外部文件 17 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF
通道 ORA_DISK_1: 将外部文件 16 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF
通道 ORA_DISK_1: 将外部文件 18 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF
通道 ORA_DISK_1: 将外部文件 19 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF
通道 ORA_DISK_1: 将外部文件 21 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF
通道 ORA_DISK_1: 将外部文件 20 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7usq5l8p_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:16
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 正在还原备份片段中的所有外部文件
通道 ORA_DISK_1: 正在读取备份片段 D:\app\oracle\oradata\tdb\db_transport_7vsq5lb
f_1_1
通道 ORA_DISK_1: 将外部文件 6 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF
通道 ORA_DISK_1: 将外部文件 5 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF
通道 ORA_DISK_1: 将外部文件 8 还原到 D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF
通道 ORA_DISK_1: 外部片段句柄 = D:\app\oracle\oradata\tdb\db_transport_7vsq5lbf_
1_1
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:16
在 02-2月 -18 完成了 restore

11.在目标平台上使用备份的控制文件来为目标数据库创建控制文件,将备分的控制文件记录的源数据文件修改成还原后的数据文件名,其创建语句如下

CREATE CONTROLFILE REUSE DATABASE "JY" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo01.log'  SIZE 200M,
  GROUP 2 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo02.log'  SIZE 200M
-- STANDBY LOGFILE
DATAFILE
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF',
  'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF'
CHARACTER SET ZHS16GBK
;

执行上面的创建语句

SQL> CREATE CONTROLFILE REUSE DATABASE "JY" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 192
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 1024
  5      MAXINSTANCES 32
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo01.log'  SIZE 200M,

  9    GROUP 2 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\redo02.log'  SIZE 200M
 10  -- STANDBY LOGFILE
 11  DATAFILE
 12    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF',
 13    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF',
 14    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF',
 15    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF',
 16    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF',
 17    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF',
 18    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF',
 19    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF',
 20    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF',
 21    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF',
 22    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF',
 23    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF',
 24    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF',
 25    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF',
 26    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF',
 27    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF',
 28    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF',
 29    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF',
 30    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF',
 31    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF',
 32    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF',
 33    'D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF'
 34  CHARACTER SET ZHS16GBK
 35  ;

控制文件已创建。


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H7HXK_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H7HXK_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H4FJF_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H7HXK_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS2_F76H4FHX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H65NX_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSTEM_F76H70CR_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_SYSAUX_F76H70CR_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDOTBS1_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_UNDO_2_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_USERS_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TEST_F76H70D8_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_TTS_F76H65OF_.DBF
D:\APP\ORACLE\ORADATA\JY\DATAFILE\O1_MF_CS_F76H65OF_.DBF

已选择 22 行。


12.以open resetlogs选项打开数据库

SQL> alter database open resetlogs;

数据库已更改。

SQL>  alter pluggable database all open read write;

插接式数据库已变更。

SQL> select name,open_mode from v$pdbs;

NAME                       OPEN_MODE
----------------------     ------------------------
PDB$SEED                   READ ONLY
JYPDB                      READ WRITE
TESTPDB                    READ WRITE

13.给CDB数据库增加临进表空间数据文件

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp01.dbf'     size 100M  reuse autoextend on next 655360  maxsize 200m;

表空间已更改。

SQL> alter session set container = pdb$seed;

会话已更改。

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp02.dbf'   size 100M  reuse autoextend on next 655360  maxsize 200m;

表空间已更改。

SQL> alter session set container = jypdb;

会话已更改。

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp03.dbf'  size 100M reuse;

表空间已更改。

SQL> alter session set container = testpdb;

会话已更改。

SQL> alter tablespace temp add tempfile 'D:\APP\ORACLE\ORADATA\JY\DATAFILE\temp04.dbf' size 100M reuse;

表空间已更改。

SQL> alter session set container = cdb$root;

会话已更改。

到此使用备份集跨平台传输整个CDB数据库就完成了。

Oracle 12C RMAN transport tablespace from PDB of RAC CDB to remote PDB

这个例子是将Linux 平台上的RAC CDB数据库中PDB(jypdb)数据库中的tts,cs表空间(tts,cs用户的缺省永久表空间)传输到Linux 平台上的单实例CDB数据库中的PDB(jypdb)中
1.确认源数据库与目标数据库所在平台是不是传输表空间所支持的操作系统平台。
确认源数据库操作系统平台:

SQL> select d.platform_name,endian_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

确认目数据库操作系统平台:

SQL> select d.platform_name,endian_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

这里操作系统平台都是64位Linux

2.确认要被传输的表空间是否是自包含表空间(tts,cs):

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

如果没有行选择,表示该表空间只包含表数据,可以传输

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        45

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        46

3.对源数据库执行备份,在执行备份时一定要加上include current controlfile否在执行

transport tablespace命令时会出现以下错误信息:
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of tranport tablespace command at 01/25/2018 15:24:22
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore


[oracle@jytest1 ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@jytest1 ~]$ rman target sys/abcd@jy  catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 25 23:30:46 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

使用RMAN连接到源RAC CDB数据库执行备份操作

RMAN> backup as compressed backupset database format '+test/rman_backup/jy_%u_%d_%t_%s_%p' include current controlfile   plus archivelog format '+test/rman_backup/arch_%d_%T_%U'  delete all input;


Starting backup at 2018-01-25 23:30:58
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1522 instance=jy2 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=141 RECID=320 STAMP=966188816
input archived log thread=1 sequence=161 RECID=321 STAMP=966188818
input archived log thread=2 sequence=142 RECID=323 STAMP=966188832
input archived log thread=1 sequence=162 RECID=322 STAMP=966188829
input archived log thread=1 sequence=163 RECID=325 STAMP=966188842
input archived log thread=2 sequence=143 RECID=324 STAMP=966188842
input archived log thread=2 sequence=144 RECID=327 STAMP=966189268
input archived log thread=1 sequence=164 RECID=326 STAMP=966189266
input archived log thread=1 sequence=165 RECID=328 STAMP=966189274
input archived log thread=2 sequence=145 RECID=329 STAMP=966189274
input archived log thread=1 sequence=166 RECID=330 STAMP=966192976
input archived log thread=2 sequence=146 RECID=331 STAMP=966192977
input archived log thread=1 sequence=167 RECID=333 STAMP=966193374
input archived log thread=2 sequence=147 RECID=332 STAMP=966193373
input archived log thread=2 sequence=148 RECID=334 STAMP=966193380
input archived log thread=1 sequence=168 RECID=335 STAMP=966193380
input archived log thread=1 sequence=169 RECID=336 STAMP=966207796
input archived log thread=2 sequence=149 RECID=338 STAMP=966208644
input archived log thread=1 sequence=170 RECID=337 STAMP=966208634
input archived log thread=1 sequence=171 RECID=339 STAMP=966208856
input archived log thread=2 sequence=150 RECID=340 STAMP=966208857
input archived log thread=1 sequence=172 RECID=341 STAMP=966209350
input archived log thread=2 sequence=151 RECID=342 STAMP=966209350
input archived log thread=1 sequence=173 RECID=344 STAMP=966209356
input archived log thread=2 sequence=152 RECID=343 STAMP=966209356
input archived log thread=2 sequence=153 RECID=346 STAMP=966246980
input archived log thread=1 sequence=174 RECID=345 STAMP=966246243
input archived log thread=1 sequence=175 RECID=347 STAMP=966271688
input archived log thread=2 sequence=154 RECID=349 STAMP=966282445
input archived log thread=1 sequence=176 RECID=348 STAMP=966282440
input archived log thread=1 sequence=177 RECID=351 STAMP=966282933
input archived log thread=2 sequence=155 RECID=350 STAMP=966282933
input archived log thread=2 sequence=156 RECID=354 STAMP=966296899
input archived log thread=1 sequence=178 RECID=352 STAMP=966296887
input archived log thread=1 sequence=179 RECID=353 STAMP=966296890
input archived log thread=2 sequence=157 RECID=355 STAMP=966331920
input archived log thread=1 sequence=180 RECID=356 STAMP=966340837
input archived log thread=2 sequence=158 RECID=359 STAMP=966382267
input archived log thread=1 sequence=181 RECID=357 STAMP=966382003
input archived log thread=1 sequence=182 RECID=358 STAMP=966382267
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:31:12
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:33:17
piece handle=+TEST/rman_backup/arch_jy_20180125_7ispjklv_1_1 tag=TAG20180125T233110 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+TEST/arch/2_141_961976319.dbf RECID=320 STAMP=966188816
archived log file name=+TEST/arch/1_161_961976319.dbf RECID=321 STAMP=966188818
archived log file name=+TEST/arch/2_142_961976319.dbf RECID=323 STAMP=966188832
archived log file name=+TEST/arch/1_162_961976319.dbf RECID=322 STAMP=966188829
archived log file name=+TEST/arch/1_163_961976319.dbf RECID=325 STAMP=966188842
archived log file name=+TEST/arch/2_143_961976319.dbf RECID=324 STAMP=966188842
archived log file name=+TEST/arch/2_144_961976319.dbf RECID=327 STAMP=966189268
archived log file name=+TEST/arch/1_164_961976319.dbf RECID=326 STAMP=966189266
archived log file name=+TEST/arch/1_165_961976319.dbf RECID=328 STAMP=966189274
archived log file name=+TEST/arch/2_145_961976319.dbf RECID=329 STAMP=966189274
archived log file name=+TEST/arch/1_166_961976319.dbf RECID=330 STAMP=966192976
archived log file name=+TEST/arch/2_146_961976319.dbf RECID=331 STAMP=966192977
archived log file name=+TEST/arch/1_167_961976319.dbf RECID=333 STAMP=966193374
archived log file name=+TEST/arch/2_147_961976319.dbf RECID=332 STAMP=966193373
archived log file name=+TEST/arch/2_148_961976319.dbf RECID=334 STAMP=966193380
archived log file name=+TEST/arch/1_168_961976319.dbf RECID=335 STAMP=966193380
archived log file name=+TEST/arch/1_169_961976319.dbf RECID=336 STAMP=966207796
archived log file name=+TEST/arch/2_149_961976319.dbf RECID=338 STAMP=966208644
archived log file name=+TEST/arch/1_170_961976319.dbf RECID=337 STAMP=966208634
archived log file name=+TEST/arch/1_171_961976319.dbf RECID=339 STAMP=966208856
archived log file name=+TEST/arch/2_150_961976319.dbf RECID=340 STAMP=966208857
archived log file name=+TEST/arch/1_172_961976319.dbf RECID=341 STAMP=966209350
archived log file name=+TEST/arch/2_151_961976319.dbf RECID=342 STAMP=966209350
archived log file name=+TEST/arch/1_173_961976319.dbf RECID=344 STAMP=966209356
archived log file name=+TEST/arch/2_152_961976319.dbf RECID=343 STAMP=966209356
archived log file name=+TEST/arch/2_153_961976319.dbf RECID=346 STAMP=966246980
archived log file name=+TEST/arch/1_174_961976319.dbf RECID=345 STAMP=966246243
archived log file name=+TEST/arch/1_175_961976319.dbf RECID=347 STAMP=966271688
archived log file name=+TEST/arch/2_154_961976319.dbf RECID=349 STAMP=966282445
archived log file name=+TEST/arch/1_176_961976319.dbf RECID=348 STAMP=966282440
archived log file name=+TEST/arch/1_177_961976319.dbf RECID=351 STAMP=966282933
archived log file name=+TEST/arch/2_155_961976319.dbf RECID=350 STAMP=966282933
archived log file name=+TEST/arch/2_156_961976319.dbf RECID=354 STAMP=966296899
archived log file name=+TEST/arch/1_178_961976319.dbf RECID=352 STAMP=966296887
archived log file name=+TEST/arch/1_179_961976319.dbf RECID=353 STAMP=966296890
archived log file name=+TEST/arch/2_157_961976319.dbf RECID=355 STAMP=966331920
archived log file name=+TEST/arch/1_180_961976319.dbf RECID=356 STAMP=966340837
archived log file name=+TEST/arch/2_158_961976319.dbf RECID=359 STAMP=966382267
archived log file name=+TEST/arch/1_181_961976319.dbf RECID=357 STAMP=966382003
archived log file name=+TEST/arch/1_182_961976319.dbf RECID=358 STAMP=966382267
Finished backup at 2018-01-25 23:33:25

Starting backup at 2018-01-25 23:33:25
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/JY/DATAFILE/sysaux.298.962209605
input datafile file number=00009 name=+DATA/JY/DATAFILE/undotbs2.312.962209605
input datafile file number=00004 name=+DATA/JY/DATAFILE/undotbs1.277.962209605
input datafile file number=00001 name=+DATA/JY/DATAFILE/system.317.962209603
input datafile file number=00007 name=+DATA/JY/DATAFILE/users.301.962209605
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:33:28
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:35:43
piece handle=+TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
input datafile file number=00011 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
input datafile file number=00013 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
input datafile file number=00010 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
input datafile file number=00012 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
input datafile file number=00015 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
input datafile file number=00022 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
input datafile file number=00023 name=+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:35:43
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:36:48
piece handle=+TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
input datafile file number=00016 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
input datafile file number=00018 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
input datafile file number=00019 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
input datafile file number=00021 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409
input datafile file number=00020 name=+DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:36:49
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:37:44
piece handle=+TEST/rman_backup/jy_7lspjl0h_jy_966382609_245_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
input datafile file number=00005 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
input datafile file number=00008 name=+DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:37:44
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:38:29
piece handle=+TEST/rman_backup/jy_7mspjl28_jy_966382664_246_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:38:33
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:38:34
piece handle=+TEST/rman_backup/jy_7nspjl3l_jy_966382709_247_1 tag=TAG20180125T233325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2018-01-25 23:38:34

Starting backup at 2018-01-25 23:38:34
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=159 RECID=361 STAMP=966382716
input archived log thread=1 sequence=183 RECID=360 STAMP=966382716
channel ORA_DISK_1: starting piece 1 at 2018-01-25 23:38:39
channel ORA_DISK_1: finished piece 1 at 2018-01-25 23:38:40
piece handle=+TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1 tag=TAG20180125T233838 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+TEST/arch/2_159_961976319.dbf RECID=361 STAMP=966382716
archived log file name=+TEST/arch/1_183_961976319.dbf RECID=360 STAMP=966382716
Finished backup at 2018-01-25 23:38:41

Starting Control File and SPFILE Autobackup at 2018-01-25 23:38:42
piece handle=+TEST/rman_backup/c-979425723-20180125-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2018-01-25 23:38:50

RMAN> list backup;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7004    696.52M    DISK        00:01:56     2018-01-25 23:33:07
        BP Key: 7005   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233110
        Piece Name: +TEST/rman_backup/arch_jy_20180125_7ispjklv_1_1

  List of Archived Logs in backup set 7004
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    161     15489267   2018-01-23 03:00:12 15615014   2018-01-23 12:00:12
  1    162     15615014   2018-01-23 12:00:12 15660438   2018-01-23 17:46:58
  1    163     15660438   2018-01-23 17:46:58 15660585   2018-01-23 17:47:21
  1    164     15660585   2018-01-23 17:47:21 15661249   2018-01-23 17:54:26
  1    165     15661249   2018-01-23 17:54:26 15661286   2018-01-23 17:54:34
  1    166     15661286   2018-01-23 17:54:34 15668074   2018-01-23 18:56:16
  1    167     15668074   2018-01-23 18:56:16 15669730   2018-01-23 19:02:53
  1    168     15669730   2018-01-23 19:02:53 15669749   2018-01-23 19:02:59
  1    169     15669749   2018-01-23 19:02:59 15725290   2018-01-23 23:03:04
  1    170     15725290   2018-01-23 23:03:04 15748445   2018-01-23 23:17:11
  1    171     15748445   2018-01-23 23:17:11 15749326   2018-01-23 23:20:56
  1    172     15749326   2018-01-23 23:20:56 15754717   2018-01-23 23:29:08
  1    173     15754717   2018-01-23 23:29:08 15754744   2018-01-23 23:29:16
  1    174     15754744   2018-01-23 23:29:16 15913424   2018-01-24 09:43:49
  1    175     15913424   2018-01-24 09:43:49 15970367   2018-01-24 16:47:52
  1    176     15970367   2018-01-24 16:47:52 15998139   2018-01-24 19:47:17
  1    177     15998139   2018-01-24 19:47:17 15999227   2018-01-24 19:55:33
  1    178     15999227   2018-01-24 19:55:33 16159305   2018-01-24 23:48:02
  1    179     16159305   2018-01-24 23:48:02 16159307   2018-01-24 23:48:02
  1    180     16160440   2018-01-24 23:54:33 16282809   2018-01-25 12:00:25
  1    181     16282809   2018-01-25 12:00:25 16387407   2018-01-25 23:26:30
  1    182     16387407   2018-01-25 23:26:30 16389194   2018-01-25 23:31:04
  2    141     15416434   2018-01-22 23:13:31 15539078   2018-01-23 07:00:05
  2    142     15539078   2018-01-23 07:00:05 15660442   2018-01-23 17:47:00
  2    143     15660442   2018-01-23 17:47:00 15660582   2018-01-23 17:47:21
  2    144     15660582   2018-01-23 17:47:21 15661253   2018-01-23 17:54:28
  2    145     15661253   2018-01-23 17:54:28 15661290   2018-01-23 17:54:34
  2    146     15661290   2018-01-23 17:54:34 15668077   2018-01-23 18:56:16
  2    147     15668077   2018-01-23 18:56:16 15669727   2018-01-23 19:02:53
  2    148     15669727   2018-01-23 19:02:53 15669752   2018-01-23 19:02:59
  2    149     15669752   2018-01-23 19:02:59 15748449   2018-01-23 23:17:13
  2    150     15748449   2018-01-23 23:17:13 15749330   2018-01-23 23:20:56
  2    151     15749330   2018-01-23 23:20:56 15754721   2018-01-23 23:29:09
  2    152     15754721   2018-01-23 23:29:09 15754741   2018-01-23 23:29:15
  2    153     15754741   2018-01-23 23:29:15 15915073   2018-01-24 09:56:08
  2    154     15915073   2018-01-24 09:56:08 15998143   2018-01-24 19:47:20
  2    155     15998143   2018-01-24 19:47:20 15999223   2018-01-24 19:55:33
  2    156     15999223   2018-01-24 19:55:33 16159321   2018-01-24 23:48:04
  2    157     16159321   2018-01-24 23:48:04 16262173   2018-01-25 09:31:46
  2    158     16262173   2018-01-25 09:31:46 16389189   2018-01-25 23:31:00

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7055    Full    596.08M    DISK        00:02:06     2018-01-25 23:35:33
        BP Key: 7100   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1
  List of Datafiles in backup set 7055
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  1       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/system.317.962209603
  3       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/sysaux.298.962209605
  4       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/undotbs1.277.962209605
  7       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/users.301.962209605
  9       Full 16390299   2018-01-25 23:33:28              NO    +DATA/JY/DATAFILE/undotbs2.312.962209605

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7056    Full    207.16M    DISK        00:00:55     2018-01-25 23:36:38
        BP Key: 7101   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1
  List of Datafiles in backup set 7056
  Container ID: 3, PDB Name: JYPDB
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  10      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
  11      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
  12      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
  13      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
  14      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
  15      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
  22      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/tts.257.966380353
  23      Full 16390754   2018-01-25 23:35:43              NO    +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/cs.294.966380783

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7057    Full    252.70M    DISK        00:00:53     2018-01-25 23:37:42
        BP Key: 7102   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7lspjl0h_jy_966382609_245_1
  List of Datafiles in backup set 7057
  Container ID: 4, PDB Name: TESTPDB
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  16      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
  17      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
  18      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
  19      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
  20      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
  21      Full 16159351   2018-01-24 23:48:08              NO    +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7058    Full    166.49M    DISK        00:00:36     2018-01-25 23:38:20
        BP Key: 7103   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7mspjl28_jy_966382664_246_1
  List of Datafiles in backup set 7058
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  5       Full 1449535    2017-12-05 23:46:58              NO    +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
  6       Full 1449535    2017-12-05 23:46:58              NO    +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
  8       Full 1449535    2017-12-05 23:46:58              NO    +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7059    Full    4.09M      DISK        00:00:04     2018-01-25 23:38:33
        BP Key: 7104   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233325
        Piece Name: +TEST/rman_backup/jy_7nspjl3l_jy_966382709_247_1
  Control File Included: Ckp SCN: 16392014     Ckp time: 2018-01-25 23:38:29

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7134    5.64M      DISK        00:00:01     2018-01-25 23:38:39
        BP Key: 7136   Status: AVAILABLE  Compressed: YES  Tag: TAG20180125T233838
        Piece Name: +TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1

  List of Archived Logs in backup set 7134
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    183     16389194   2018-01-25 23:31:04 16392023   2018-01-25 23:38:35
  2    159     16389189   2018-01-25 23:31:00 16392045   2018-01-25 23:38:36

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7145    Full    19.06M     DISK        00:00:04     2018-01-25 23:38:47
        BP Key: 7148   Status: AVAILABLE  Compressed: NO  Tag: TAG20180125T233843
        Piece Name: +TEST/rman_backup/c-979425723-20180125-01
  SPFILE Included: Modification time: 2018-01-25 22:01:06
  SPFILE db_unique_name: JY
  Control File Included: Ckp SCN: 16392079     Ckp time: 2018-01-25 23:38:43

4.创建一个数据库目录对象用来执行Data Pump导出(/ora_backup/tts/dump),一个辅助目录(/ora_backup/tts/auxi),一个传输表空间目录(/ora_backup/tts/tbs)

[oracle@jytest1 tts]$ mkdir dump
[oracle@jytest1 tts]$ mkdir tbs
[oracle@jytest1 tts]$ mkdir auxi

SQL> conn sys/abcd@jy as sysdba
Connected.
SQL> create or replace directory test_dump as '/ora_backup/tts/dump';

Directory created.

SQL>
SQL> grant read,write on directory test_dump to public;

Grant succeeded.

5.使用RMAN连接到源RAC CDB数据库执行传输表空间操作

[oracle@jytest1 ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@jytest1 ~]$ rman target sys/abcd@jy  catalog rco/abcd@jypdb_173

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jan 26 00:32:53 2018

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

connected to target database: JY (DBID=979425723)
connected to recovery catalog database

RMAN> transport tablespace JYPDB:"TTS",JYPDB:"CS" tablespace destination '/ora_backup/tts/tbs' auxiliary destination '/ora_backup/tts/auxi'  datapump directory test_dump  dump file 'tts.dmp'  import script 'importtts.sql'  export log 'ttsexport.log';


Creating automatic instance, with SID='jBqz'

initialization parameters used for automatic instance:
db_name=JY
db_unique_name=jBqz_pitr_JYPDB_JY
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=2048M
processes=200
db_create_file_dest=/ora_backup/tts/auxi
log_archive_dest_1='location=/ora_backup/tts/auxi'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance JY

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     8794848 bytes
Variable Size                553651488 bytes
Database Buffers            1577058304 bytes
Redo Buffers                   7979008 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  scn 16412368;
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2018-01-26 00:34:32
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=211 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/c-979425723-20180125-01
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/c-979425723-20180125-01 tag=TAG20180125T233843
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:38
output file name=/ora_backup/tts/auxi/JY/controlfile/o1_mf_f6n1rxq2_.ctl
Finished restore at 2018-01-26 00:35:11

sql statement: alter database mount clone database

sql statement: alter system archive log current

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  scn 16412368;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  10 to new;
set newname for clone datafile  1 to new;
set newname for clone datafile  12 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  13 to new;
set newname for clone datafile  9 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  11 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  3 to new;
set newname for datafile  22 to
 "/ora_backup/tts/tbs/o1_mf_tts_%u_.dbf";
set newname for datafile  23 to
 "/ora_backup/tts/tbs/o1_mf_cs_%u_.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  10, 1, 12, 4, 13, 9, 3, 11, 22, 23;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /ora_backup/tts/auxi/JY/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2018-01-26 00:35:28
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /ora_backup/tts/auxi/JY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /ora_backup/tts/auxi/JY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/jy_7jspjkq7_jy_966382407_243_1 tag=TAG20180125T233325
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to /ora_backup/tts/tbs/o1_mf_tts_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to /ora_backup/tts/tbs/o1_mf_cs_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/jy_7kspjkuf_jy_966382543_244_1 tag=TAG20180125T233325
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 2018-01-26 00:39:58

datafile 10 switched to datafile copy
input datafile copy RECID=14 STAMP=966386398 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_f6n20mok_.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_system_f6n1tjl2_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=16 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_f6n20mpv_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs1_f6n1tjjq_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=18 STAMP=966386399 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_f6n20mn5_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=966386400 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs2_f6n1tjhj_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=966386400 file name=/ora_backup/tts/auxi/JY/datafile/o1_mf_sysaux_f6n1tjfj_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=21 STAMP=966386400 file name=/ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_f6n20mlb_.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=22 STAMP=966386400 file name=/ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=23 STAMP=966386401 file name=/ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 16412368;
# online the datafiles restored or switched
sql clone 'JYPDB' "alter database datafile
 10 online";
sql clone "alter database datafile  1 online";
sql clone 'JYPDB' "alter database datafile
 12 online";
sql clone "alter database datafile  4 online";
sql clone 'JYPDB' "alter database datafile
 13 online";
sql clone "alter database datafile  9 online";
sql clone "alter database datafile  3 online";
sql clone 'JYPDB' "alter database datafile
 11 online";
sql clone 'JYPDB' "alter database datafile
 22 online";
sql clone 'JYPDB' "alter database datafile
 23 online";
# recover and open resetlogs
recover clone database tablespace  "JYPDB":"TTS", "JYPDB":"CS", "JYPDB":"SYSTEM", "SYSTEM", "JYPDB":"UNDOTBS1", "UNDOTBS1", "JYPDB":"UNDO_2", "UNDOTBS2", "SYSAUX", "JYPDB":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  10 online

sql statement: alter database datafile  1 online

sql statement: alter database datafile  12 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  13 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  11 online

sql statement: alter database datafile  22 online

sql statement: alter database datafile  23 online

Starting recover at 2018-01-26 00:40:04
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 184 is already on disk as file +TEST/arch/1_184_961976319.dbf
archived log for thread 2 with sequence 160 is already on disk as file +TEST/arch/2_160_961976319.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=159
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=183
channel ORA_AUX_DISK_1: reading from backup piece +TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1
channel ORA_AUX_DISK_1: piece handle=+TEST/rman_backup/arch_jy_20180125_7ospjl3u_1_1 tag=TAG20180125T233838
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/ora_backup/tts/auxi/2_159_961976319.dbf thread=2 sequence=159
archived log file name=/ora_backup/tts/auxi/1_183_961976319.dbf thread=1 sequence=183
channel clone_default: deleting archived log(s)
archived log file name=/ora_backup/tts/auxi/1_183_961976319.dbf RECID=363 STAMP=966386408
archived log file name=+TEST/arch/1_184_961976319.dbf thread=1 sequence=184
channel clone_default: deleting archived log(s)
archived log file name=/ora_backup/tts/auxi/2_159_961976319.dbf RECID=362 STAMP=966386408
archived log file name=+TEST/arch/2_160_961976319.dbf thread=2 sequence=160
media recovery complete, elapsed time: 00:01:36
Finished recover at 2018-01-26 00:41:44

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database  JYPDB open';
}
executing Memory Script

sql statement: alter pluggable database  JYPDB open

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'JYPDB' 'alter tablespace
 "TTS" read only';
sql clone 'JYPDB' 'alter tablespace
 "CS" read only';
}
executing Memory Script

sql statement: alter tablespace  "TTS" read only

sql statement: alter tablespace  "CS" read only

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_jBqz_ufoz":
   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> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.02.00.00.00',newblock)
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 10846
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.02.00.00.00',newblock)
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '+DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 10846
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

   EXPDP> Master table "SYS"."TSPITR_EXP_jBqz_ufoz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_jBqz_ufoz is:
   EXPDP>   /ora_backup/tts/dump/tts.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace CS:
   EXPDP>   /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf
   EXPDP> Datafiles required for transportable tablespace TTS:
   EXPDP>   /ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_jBqz_ufoz" completed with 2 error(s) at Fri Jan 26 00:45:41 2018 elapsed 0 00:02:14
Export completed

Not performing table import after point-in-time recovery
/*
   The following command may be used to import the tablespaces.
   Substitute values for  and .

   impdp  directory= dumpfile= 'tts.dmp' transport_datafiles= /ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf, /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf
*/

--
--
--
--
CREATE DIRECTORY STREAMS$DIROBJ$1 AS  '/ora_backup/tts/tbs/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
--
  tbs_files     dbms_streams_tablespace_adm.file_set;
  cvt_files     dbms_streams_tablespace_adm.file_set;

--
  dump_file     dbms_streams_tablespace_adm.file;
  dp_job_name   VARCHAR2(30) := NULL;

--
  ts_names       dbms_streams_tablespace_adm.tablespace_set;
BEGIN
--
  dump_file.file_name :=  'tts.dmp';
  dump_file.directory_object := 'test_dump';

--
  tbs_files( 1).file_name :=  'o1_mf_tts_f6n20mr5_.dbf';
  tbs_files( 1).directory_object :=  'STREAMS$DIROBJ$1';

  tbs_files( 2).file_name :=  'o1_mf_cs_f6n20mso_.dbf';
  tbs_files( 2).directory_object :=  'STREAMS$DIROBJ$1';

--
  dbms_streams_tablespace_adm.attach_tablespaces(
    datapump_job_name      => dp_job_name,
    dump_file              => dump_file,
    tablespace_files       => tbs_files,
    converted_files        => cvt_files,
    tablespace_names       => ts_names);

--
  IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
    FOR i IN ts_names.first .. ts_names.last LOOP
      dbms_output.put_line('imported tablespace '|| ts_names(i));
    END LOOP;
  END IF;
END;
/

--
DROP DIRECTORY STREAMS$DIROBJ$1;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_temp_f6n28lj8_.tmp deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_temp_f6n28clb_.tmp deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_4_f6n2699f_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_3_f6n2698j_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_2_f6n2697p_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/onlinelog/o1_mf_1_f6n26971_.log deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_sysaux_f6n20mlb_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_sysaux_f6n1tjfj_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs2_f6n1tjhj_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undo_2_f6n20mn5_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_undotbs1_f6n1tjjq_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_undotbs1_f6n20mpv_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/datafile/o1_mf_system_f6n1tjl2_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/5F9AC6865E87549FE053AB828A0ADE94/datafile/o1_mf_system_f6n20mok_.dbf deleted
auxiliary instance file /ora_backup/tts/auxi/JY/controlfile/o1_mf_f6n1rxq2_.ctl deleted

上面的传输表空间操作完成后生成了传输表空间的数据文件/ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf,/ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf和Data Pump导出文件dmpfile/ora_backup/tts/dump/tts.dmp,由于我将目标主机的/ora_backup/tts/目录通过nfs挂载成源主机上的/ora_backup/tts/目录,所以这里不需要将这些文件复制就可以在目标主机上访问这些文件。

5.在目标主机上创建相关用户及Data Pump目录(这个目录应该在PDB数据库(jypdb)中创建)并将表空间附加到目标数据库中

SQL> alter session set container=jypdb;

Session altered.

SQL> create or replace directory test_dump as '/ora_backup/tts/dump';

Directory created.

SQL> grant read,write on directory test_dump to public;

Grant succeeded.


SQL> create user tts identified by "tts";

User created.

SQL> grant dba,connect,resource to tts;

Grant succeeded.

SQL> create user cs identified by "cs";

User created.

SQL> grant dba,connect,resource to cs;

Grant succeeded.

使用system用户连接到PDB数据库(jypdb)执行下面的导入操作

[oracle@shard1 admin]$ impdp system/abcd@jypdb directory=test_dump dumpfile= 'tts.dmp' transport_datafiles= /ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf, /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf

Import: Release 12.2.0.1.0 - Production on Thu Jan 25 19:03:34 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@jypdb directory=test_dump dumpfile=tts.dmp transport_datafiles=/ora_backup/tts/tbs/o1_mf_tts_f6n20mr5_.dbf, /ora_backup/tts/tbs/o1_mf_cs_f6n20mso_.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Jan 25 19:04:06 2018 elapsed 0 00:00:29

6.检查数据来验证传输表空间是否成功

SQL> select count(*) from tts.t1;

  COUNT(*)
----------
        45

SQL> select count(*) from cs.t2;

  COUNT(*)
----------
        46

与表空间传输之前的状态一致,最后还需要导入用户tts,cs下的其它元数据这里不再详细介绍具体的操作了。

Proudly powered by WordPress | Indrajeet by Sus Hill.