如何使用RMAN對CDB中的部分表空間進行復制

155次閱讀

共計 13151 個字符，預計需要花費 33 分鐘才能閱讀完成。

這篇文章主要介紹“如何使用 RMAN 對 CDB 中的部分表空間進行復制”，在日常操作中，相信很多人在如何使用 RMAN 對 CDB 中的部分表空間進行復制問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”如何使用 RMAN 對 CDB 中的部分表空間進行復制”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

1. 檢查源 RAC CDB(jy)的參數文件，數據文件，聯機日志文件，控制文件，歸檔日志文件的存儲目錄

SQL  show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- --------------------------------------------
spfile string +DATA/JY/PARAMETERFILE/spfile.303.961976713
SQL  select name from v$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

NAME TYPE VALUE
------------------------------------ ----------- -------------------------------------------
control_files string +DATA/JY/CONTROLFILE/current.272.961976315

--------------------------------------------------------------------------------
+DATA/JY/ONLINELOG/group_2.302.961976321
+DATA/JY/ONLINELOG/group_1.261.961976319
+DATA/JY/ONLINELOG/group_3.263.961976697
+DATA/JY/ONLINELOG/group_4.262.961976705

Archive destination +TEST/arch
Oldest online log sequence 127
Next log sequence to archive 128
Current log sequence 128

2. 查詢整個 CDB 中的表空間名稱, 在復制整個 CDB 數據庫時跳過 JYPDB:TEST 表空間

[oracle@jytest1 ~]$ rman target sys/xxzx7817600@jy catalog rco/xxzx7817600@jypdb_173
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jan 20 00:47:19 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  report schema;
Report of database schema for database with db_unique_name JY
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 840 SYSTEM YES +DATA/JY/DATAFILE/system.317.962209603
3 2210 SYSAUX NO +DATA/JY/DATAFILE/sysaux.298.962209605
4 570 UNDOTBS1 YES +DATA/JY/DATAFILE/undotbs1.277.962209605
5 250 PDB$SEED:SYSTEM NO +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/system.256.962209675
6 340 PDB$SEED:SYSAUX NO +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/sysaux.270.962209675
7 5 USERS NO +DATA/JY/DATAFILE/users.301.962209605
8 100 PDB$SEED:UNDOTBS1 NO +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/undotbs1.296.962209675
9 150 UNDOTBS2 YES +DATA/JY/DATAFILE/undotbs2.312.962209605
10 270 JYPDB:SYSTEM YES +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/system.271.962209649
11 590 JYPDB:SYSAUX NO +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/sysaux.316.962209649
12 100 JYPDB:UNDOTBS1 YES +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undotbs1.264.962209649
13 100 JYPDB:UNDO_2 YES +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/undo_2.268.962209649
14 1267 JYPDB:USERS NO +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/users.278.962209649
15 100 JYPDB:TEST NO +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATAFILE/test.275.962210609
16 260 TESTPDB:SYSTEM YES +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/system.260.962469409
17 550 TESTPDB:SYSAUX NO +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/sysaux.259.962469409
18 100 TESTPDB:UNDOTBS1 YES +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undotbs1.265.962469409
19 100 TESTPDB:UNDO_2 YES +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/undo_2.266.962469409
20 5 TESTPDB:USERS NO +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/users.267.962469409
21 100 TESTPDB:TEST NO +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/test.269.962469409
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 144 TEMP 32767 +DATA/JY/TEMPFILE/temp.299.961976339
2 64 PDB$SEED:TEMP 32767 +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/TEMPFILE/temp.297.962209865
3 137 JYPDB:TEMP 32767 +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/TEMPFILE/temp.276.962210519
4 135 TESTPDB:TEMP 32767 +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/TEMPFILE/temp.258.962469435

3. 在目標主機上創建存儲目標數據庫 (dupjy) 相關數據庫文件的目錄，目標主機上的 /u01/app/oracle/oradata/dupjy 目錄用來存儲數據庫的數據文件，控制文件，聯機重做日志文件),/u01/app/oracle/arch/dupjy 目錄用來存儲數據庫的歸檔重做日志文件。

4. 將源數據庫的密碼文件復制到目標主機的相應目錄中并修改為目標數據庫 (dupjy) 對應的文件名

[grid@jytest1 ~]$ srvctl config database -db jy
Database unique name: jy
Database name: jy
Oracle home: /u01/app/oracle/product/12.2.0/db
Oracle user: oracle
Spfile: +DATA/JY/PARAMETERFILE/spfile.303.961976713
Password file: +DATA/JY/PASSWORD/pwdjy.274.961976109
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: jy1,jy2
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 ~]$ asmcmd cp +DATA/JY/PASSWORD/pwdjy.274.961976109 /home/grid/pwddupjy
copying +DATA/JY/PASSWORD/pwdjy.274.961976109 -  /home/grid/pwddupjy

[oracle@shard1 dbs]# scp grid@10.138.130.171:/home/grid/pwddupjy /u01/app/oracle/product/12.2.0/db/dbs/
The authenticity of host  10.138.130.171 (10.138.130.171)  can t be established.
ECDSA key fingerprint is 7a:62:58:8b:77:98:52:94:d6:d5:0c:c4:6c:87:a6:7f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added  10.138.130.171  (ECDSA) to the list of known hosts.
grid@10.138.130.171 s password:
pwddupjy
[oracle@shard1 dbs]$ mv pwddupjy orapwdupjy

5. 使用源數據庫的 spfile 文件來創建目標數據庫要使用的 spfile 文件
在源數據庫中執行下面的命令來創建 pfile 參數文件

SQL  create pfile from spfile;
File created.
[root@shard1 ~]# scp oracle@10.138.130.171:/u01/app/oracle/product/12.2.0/db/dbs/initjy1.ora /u01/app/oracle/product/12.2.0/db/dbs/
grid@10.138.130.171 s password:
initjy1.ora 100% 1731 1.7KB/s 00:00
[root@shard1 ~]#

[oracle@shard1 dbs]$ cat initdupjy.ora
*.audit_file_dest= /u01/app/oracle/admin/dupjy/adump 
*.cluster_database=false
*.compatible= 12.2.0 
*.control_files= /u01/app/oracle/oradata/dupjy/control01.ctl 
*.db_block_size=8192
*.db_name= dupjy 
*.diagnostic_dest= /u01/app/oracle 
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=1g
*.processes=2000
*.remote_login_passwordfile= exclusive 
*.sga_max_size=2147483648
*.sga_target=2147483648
*.log_archive_dest_1= location=/u01/app/oracle/oradata/arch_dupjy 
db_file_name_convert= (+DATA/JY/DATAFILE/ , /u01/app/oracle/oradata/dupjy/ , +DATA/JY/5F9AA264B21F3ED9E053AB828A0A6088/DATAFILE/ , /u01/app/oracle/oradata/dupjy/ , +DATA/JY/5F9AC6865E87549FE053AB828A0ADE94/DATA FILE/ , /u01/app/oracle/oradata/dupjy/ , +DATA/JY/600D6F56DEB669CCE053AB828A0AAB7E/DATAFILE/ , /u01/app/oracle/oradata/dupjy/)
log_file_name_convert=(+DATA/JY/ONLINELOG/ , /u01/app/oracle/oradata/dupjy/)

[oracle@shard1 dbs]$ export ORACLE_SID=dupjy
[oracle@shard1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 18 19:06:00 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL  startup nomount pfile= /u01/app/oracle/product/12.2.0/db/dbs/initdupjy.ora 
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 570428704 bytes
Database Buffers 1560281088 bytes
Redo Buffers 7979008 bytes
SQL  create spfile from pfile= /u01/app/oracle/product/12.2.0/db/dbs/initdupjy.ora 
File created.

6. 使用 spfile 參數文件來啟動目標數據庫實例(輔助實例 dupjy)

SQL  shutdown immediate
ORA-01507: database not mounted

Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 570428704 bytes
Database Buffers 1560281088 bytes
Redo Buffers 7979008 bytes

NAME TYPE VALUE
------------------------------------ ---------------------- -----------------------------------------------------
spfile string /u01/app/oracle/product/12.2.0/db/dbs/spfiledupjy.ora

7. 給目標數據庫配置靜態監聽

[oracle@shard1 admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = shard1)(PORT = 1521))
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 )
 )
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = dupjy)
 (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db)
 (GLOBAL_DBNAME=dupjy)
 )
 )

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-JAN-2018 20:30:34
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shard1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 18-JAN-2018 20:30:34
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service  dupjy  has 1 instance(s).
 Instance  dupjy , status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

8. 在目標主機上配置 tns，用來通過網絡服務名來連接源數據庫與目標數據庫

[oracle@shard1 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.
JY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.171)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = jy)
 )
 )
DUPJY =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.180)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = dupjy)
 )
 )

9. 在目標主機上測試是否可以通過使用網絡服務名來連接源數據庫與目標數據庫

[oracle@shard1 admin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 18 21:02:53 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL  conn / as sysdba
Connected.
SQL  startup nomount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8794848 bytes
Variable Size 570428704 bytes
Database Buffers 1560281088 bytes
Redo Buffers 7979008 bytes
SQL  exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@shard1 admin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 18 21:04:51 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL  conn sys/abcd@dupjy as sysdba
Connected.
SQL  conn sys/abcd@jy as sysdba
Connected.

10. 在源主機上配置目標數據庫的 tns 名

[oracle@jytest1 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.

 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = jytest-scan)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = jy)
 )
 )

 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.180)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = dupjy)
 (UR = A)
 )
 )

11. 在源主機上測試能否可以使用網絡服務名來連接源數據庫與目標數據庫

[oracle@jytest1 admin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 19 03:16:59 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL  conn sys/abcd@jy as sysdba
Connected.
SQL  conn sys/abcd@dupjy as sysdba
Connected.

12. 執行數據庫復制(跳過源 CDB 中的 JYPDB:TEST 表空間)

[oracle@jytest1 ~]$ rman target sys/abcd@jy catalog rco/abcd@jypdb_173 auxiliary sys/abcd@dupjy
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jan 20 01:14:06 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
connected to auxiliary database: DUPJY (not mounted)

正文完