环境说明:
1)数据库版本 12.2.0.1.0
2)db_name为cndba,db_unique_name 分别为 pdbcndba_p(主库),pdbcndba_s(备库1),pdbcndba_c(备库2)
3)主库地址 192.168.1.74,备库1地址 192.168.1.75,备库2地址 192.168.1.78
4)一个备库搭建和多个备库搭建方法一样,配置修改可参考下面链接对主备库修改。
http://www.cndba.cn/leo1990/article/1935
下面主要介绍 tnsname.ora 和 pfile 参数文件 文件的修改(其他配置完成之后,配置重点就这两个文件)
1)主库修改参数文件
[oracle@www.cndba.cn dbs]$ pwd
/u01/app/oracle/product/12.2.0/db_1/dbs
[oracle@www.cndba.cn dbs]$ cat initcndba.ora
添加一下内容,注意log_archive_dest_3 用于指定备库2的参数,如果备库多个话,继续使用参数log_archive_dest_n指定备库
--修改主库参数
*.db_name='cndba'
*.db_unique_name='pdbcndba_p'
*.log_archive_config='dg_config=(pdbcndba_p,pdbcndba_s,pdbcndba_c)'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=pdbcndba_p'
*.log_archive_dest_2='service=pdbcndba_s lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s'
*.log_archive_dest_3='service=pdbcndba_c lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_c'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_dest_state_3=enable
*.standby_file_management='auto'
*.fal_server='pdbcndba_s,pdbcndba_c'
*.FAL_CLIENT='pdbcndba_p'
*.log_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba'
*.db_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba'
--注意这 2 个参数。这个 2 个参数,只在数据库为 standby_role 时才生效,但我们在主库还是配置这 2 个参数,
就是为了减少以后做 switchover 的时间。 注意 2 个目录的结构。 前面是发送数据的,后面的接收数据的。因为我所有的机器都是实例都是cndba 所以不好区别。
--下面举例配置log_file_name_convert,db_file_name_convert
cndba_p(主库) cndba_s(备库1) cndba_c(备库2)
'/u01/app/oracle/oradata/cndba_p','/u01/app/oracle/oradata/cndba_p','/u01/app/oracle/oradata/cndba_c','/u01/app/oracle/oradata/cndba_p'
'/u01/app/oracle/oradata/cndba_p','/u01/app/oracle/oradata/cndba_s','/u01/app/oracle/oradata/cndba_c','/u01/app/oracle/oradata/cndba_s'
'/u01/app/oracle/oradata/cndba_p','/u01/app/oracle/oradata/cndba_c','/u01/app/oracle/oradata/cndba_s','/u01/app/oracle/oradata/cndba_c'
--修改备库1 参数
*.db_name='cndba'
*.db_unique_name='pdbcndba_s'
*.log_archive_config='dg_config=(pdbcndba_p,pdbcndba_s,pdbcndba_c)'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=pdbcndba_s'
*.log_archive_dest_2='service=pdbcndba_p lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p'
*.log_archive_dest_3='service=pdbcndba_c lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_c'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_dest_state_3=enable
*.standby_file_management='auto'
*.fal_server='pdbcndba_p,pdbcndba_c'
*.FAL_CLIENT='pdbcndba_s'
*.log_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba'
*.db_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba'
--修改备库2 参数
*.db_name='cndba'
*.db_unique_name='pdbcndba_c'
*.log_archive_config='dg_config=(pdbcndba_p,pdbcndba_s,pdbcndba_c)'
*.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=pdbcndba_c'
*.log_archive_dest_2='service=pdbcndba_p lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_p'
*.log_archive_dest_3='service=pdbcndba_s lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=pdbcndba_s'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_dest_state_3=enable
*.standby_file_management='auto'
*.fal_server='pdbcndba_p,pdbcndba_s'
*.FAL_CLIENT='pdbcndba_c'
*.log_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba'
*.db_file_name_convert='/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba','/u01/app/oracle/oradata/cndba'
2)分别在主备库配置tnsnames.ora ,添加以下内容并且配置完成后,使用tnsping 命令校验,切记要相互ping 通方可继续后面的操作
PDBCNDBA_P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.74)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
PDBCNDBA_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.75)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
PDBCNDBA_C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.78)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
3)参考文档里介绍了,Active duplicate 这里是备库的用同样的方法复制主库数据
[oracle@www.cndba.cn ~]$ rman target sys/oracle@pdbcndba_p auxiliary sys/oracle@pdbcndba_c;
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Aug 16 23:39:26 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: CNDBA (DBID=215561032)
connected to auxiliary database: CNDBA (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
Starting Duplicate Db at 16-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0/db_1/dbs/orapwcndba' auxiliary format
'/u01/app/oracle/product/12.2.0/db_1/dbs/orapwcndba' ;
}
executing Memory Script
Starting backup at 16-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
Finished backup at 16-AUG-17
contents of Memory Script:
{
restore clone from service 'pdbcndba_p' standby controlfile;
}
executing Memory Script
Starting restore at 16-AUG-17
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 pdbcndba_p
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:11
output file name=/u01/app/oracle/oradata/cndba/control01.ctl
output file name=/u01/app/oracle/oradata/cndba/control02.ctl
Finished restore at 16-AUG-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/cndba/temp01.dbf";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/cndba/pdbseed/temp012017-08-04_01-59-39-412-AM.dbf";
set newname for tempfile 3 to
"/u01/app/oracle/oradata/cndba/pdbcndba/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/cndba/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/cndba/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/cndba/undotbs01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/cndba/pdbseed/system01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/cndba/users01.dbf";
set newname for datafile 8 to
"/u01/app/oracle/oradata/cndba/pdbseed/undotbs01.dbf";
set newname for datafile 9 to
"/u01/app/oracle/oradata/cndba/pdbcndba/system01.dbf";
set newname for datafile 10 to
"/u01/app/oracle/oradata/cndba/pdbcndba/sysaux01.dbf";
set newname for datafile 11 to
"/u01/app/oracle/oradata/cndba/pdbcndba/undotbs01.dbf";
set newname for datafile 12 to
"/u01/app/oracle/oradata/cndba/pdbcndba/users01.dbf";
restore
from nonsparse from service
'pdbcndba_p' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/cndba/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/cndba/pdbseed/temp012017-08-04_01-59-39-412-AM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/cndba/pdbcndba/temp01.dbf 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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 16-AUG-17
using channel ORA_AUX_DISK_1
skipping datafile 5; already restored to SCN 1441541
skipping datafile 6; already restored to SCN 1441541
skipping datafile 8; already restored to SCN 1441541
skipping datafile 9; already restored to SCN 3214897
skipping datafile 10; already restored to SCN 3214897
skipping datafile 11; already restored to SCN 3214897
skipping datafile 12; already restored to SCN 3214897
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service pdbcndba_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cndba/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service pdbcndba_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cndba/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service pdbcndba_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cndba/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service pdbcndba_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/cndba/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-AUG-17
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'pdbcndba_p'
archivelog from scn 3324320;
switch clone datafile all;
}
executing Memory Script
Starting restore at 16-AUG-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service pdbcndba_p
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=73
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service pdbcndba_p
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=74
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-AUG-17
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/pdbcndba/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/pdbcndba/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/pdbcndba/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=952213309 file name=/u01/app/oracle/oradata/cndba/pdbcndba/users01.dbf
contents of Memory Script:
{
set until scn 3333292;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 16-AUG-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 73 is already on disk as file /u01/archive/1_73_951098251.dbf
archived log for thread 1 with sequence 74 is already on disk as file /u01/archive/1_74_951098251.dbf
archived log file name=/u01/archive/1_73_951098251.dbf thread=1 sequence=73
archived log file name=/u01/archive/1_74_951098251.dbf thread=1 sequence=74
media recovery complete, elapsed time: 00:00:07
Finished recover at 16-AUG-17
Finished Duplicate Db at 16-AUG-17
4)校验数据
--主库操作
[oracle@www.cndba.cn dbs]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 17 00:07:09 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> alter pluggable database pdbcndba open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBCNDBA READ WRITE NO
--备库1 操作
[oracle@www.cndba.cn ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 17 01:11:32 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected
SQL> alter pluggable database pdbcndba open;
Pluggable database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
--备库2操作
[oracle@www.cndba.cn ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 17 01:11:32 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> alter pluggable database pdbcndba open;
Pluggable database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
--主库创建表并切换日志
SQL> alter session set container=pdbcndba;
Session altered.
SQL> create table test as select * from dba_users;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
39
SQL> conn /as sysdba
Connected.
System altered.
SQL> alter system switch logfile;
System altered.
--备库1 数据同步
SQL> alter session set container=pdbcndba;
Session altered.
SQL> select count(*) from test;
COUNT(*)
----------
39
--备库2,数据同步
SQL> alter session set container=pdbcndba;
Session altered.
SQL> select count(*) from test;
COUNT(*)
----------
39