如何使用RMAN对CDB中的部分表空间进行复制

139次阅读

共计 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)

正文完