oracle数据库复制操作方法
最近更新：2024-03-29 11:55:44
一、准备辅助实例
1、启动辅助实例
[oracle@19c02 ~]$ export ORACLE_SID=test
[oracle@19c02 ~]$ cat pfile.ora
db_name=orcl
ENABLE_PLUGGABLE_DATABASE=TRUE
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 356512392 bytes
Fixed Size 9134728 bytes
Variable Size 289406976 bytes
Database Buffers 50331648 bytes
Redo Buffers 7639040 bytes
此处复制的pdb,所以需要提前加入参数ENABLE_PLUGGABLE_DATABASE=TRUE
2、配置连接串
[oracle@19c02 admin]$ cat tnsnames.ora
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.220.202)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
PRODCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.220.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRODCDB)
)
)
3、配置静态监听
[oracle@19c02 ~]$ rman target sys/oracle@prodcdb auxiliary /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 7 11:29:59 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODCDB (DBID=3160879607)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database to orcl from active database password file;
Starting Duplicate Db at 07-MAR-24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/07/2024 11:30:25
RMAN-05501: aborting duplication of target database
RMAN-06217: not connected to auxiliary database with a net service name
根据以上报错可以看出来异机复制的时候需要通过service name连接辅助库，在数据库只是处于nomount状态时连接，只有通过静态监听了
[oracle@19c02 admin]$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.220.202)(PORT = 1526))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl)
(SID_NAME=test)
(ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
)
)
根据模板随便改一个，注意ORACLE_HOME和SID_NAME填写正确即可，其它的随便填。
[oracle@19c02 admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 07-MAR-2024 11:33:35
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/19c02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.220.202)(PORT=1526)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.220.202)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 07-MAR-2024 11:33:35
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/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/19c02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.220.202)(PORT=1526)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
现在可以发现静态监听里面的GLOBAL_DBNAME只是被监听这里拿来当作服务名用，随便填写即可，主要tnsname.ora种的service_name需要和这里保持一致。
4、配置密码文件
需要造一个密码文件，因为复制到远程主机或者活动数据库复制时密码文件是必须的。在复制备库时会自动复制密码文件，但是其它复制情况下只有指定passwrd file参数才会复制密码文件。并且覆盖当前实例的密码文件。
关于创建密码文件，官方文档给出来4种方法 1、当在同机复制数据库时，直接复制目标库的密码文件并改名位辅助库的密码文件名即可。 2、手动创建密码文件。保证目标数据库和辅助实例中的SYSDBA和SYSBACKUP用户的密码相同。 3、使用orapwd创建一个sysbackup条目。
orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2
4、复制时指定password file。但是因为异机复制需要提前准备密码文件，使用fuplicate复制过来的密码文件只是覆盖之前的密码文件而已。
[oracle@19c02 dbs]$ orapwd FILE='/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwtest' FORMAT=12.2 password=P@ssw0rd
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM SYSBACKUP SYSDG SYSKM ACCOUNT_ST PASSWORD_PROFILE LAST_LOGIN LOCK_DATE EXPIRY_DATE EXTERNAL_NAME AUTHENTICATION_T COMMON CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ------------ ------------ -------------------- ---------------- ------ ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD YES 0
SYSTEM FALSE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD NO 1
RMAN> duplicate target database to orcl from active database password file;
Starting Duplicate Db at 07-MAR-24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/07/2024 13:07:38
RMAN-05501: aborting duplication of target database
RMAN-05614: Passwords for target and auxiliary connections must be the same when using active duplicate
还要求目标库和辅助库密码相同
二、启动复制
1、有趣的报错
[oracle@19c02 admin]$ rman target sys/oracle@prodcdb auxiliary sys/oracle@orcl
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 7 13:20:45 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODCDB (DBID=3160879607)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database to orcl from active database password file;
Starting Duplicate Db at 07-MAR-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=292 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwtest' ;
sql clone "create spfile from memory";
}
executing Memory Script
Starting backup at 07-MAR-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/07/2024 13:21:52
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/07/2024 13:21:52
ORA-17627: ORA-12541: TNS:no listener
ORA-17629: Cannot connect to the remote database server
这里报错无法连接目标库。检查数据库发现目标库和辅助库都使用静态监听、tnsnames.ora文件种均配置了目标库和辅助库。这里报错经过检查发现tnsnames.ora里server name 配置错误。
2、password file子命令
[oracle@19c02 admin]$ rman target sys/oracle@prodcdb auxiliary sys/oracle@orcl
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 7 13:27:37 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODCDB (DBID=3160879607)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database to orcl from active database;
Starting Duplicate Db at 07-MAR-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=8 device type=DISK
current log archived
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 356512392 bytes
Fixed Size 9134728 bytes
Variable Size 289406976 bytes
Database Buffers 50331648 bytes
Redo Buffers 7639040 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PRODCDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''orcl'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone from service 'prodcdb' primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PRODCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''orcl'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 356512392 bytes
Fixed Size 9134728 bytes
Variable Size 289406976 bytes
Database Buffers 50331648 bytes
Redo Buffers 7639040 bytes
Starting restore at 07-MAR-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=424 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service prodcdb
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02......
登录MES账号 查看全文详情
登录/注册
