oracle DG 主备库为RAC及一个主库对多个从库的实验环境搭建

发布时间：2020-08-09 20:18:13 来源：网络阅读：2617 作者：llc018198 栏目：关系型数据库

主库 RAC ：192.168.1.210 node1

192.168.1.211 node2

备库（1） RAC: 192.168.1.247 rac1

192.168.1.248 rac2

备库（2）单实例：192.168.1.219 dataguard

以上均为ASM管理。

实验步骤：

配置备库（1)的静态监听：
SID_LIST_LISTENER=
       (SID_LIST=
       (SID_DESC=
       (GLOBAL_DBNAME=SMS)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
       (SID_NAME=SMS1)
         )
        )（rac1)
SID_LIST_LISTENER=
       (SID_LIST=
       (SID_DESC=
       (GLOBAL_DBNAME=SMS)
       (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
       (SID_NAME= SMS2)
         )
        )(rac2)
配置完成后，重启监听：
在主库TNS文件后追加：
SMS =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.220)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
        (SERVICE_NAME = SMS)
    )
)
SMS1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.247)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
        (SERVICE_NAME = SMS)
      (SERVICE_NAME = SMS1)
    )
)
SMS2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.248)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
        (SERVICE_NAME = SMS)
     (SERVICE_NAME = SMS2)
    )
)
PHUB =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.219)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PHUB)
    )
)
复制过程：
RMAN> @duplicate.sql
RMAN> duplicate target database
2> for standby
3> from active database nofilenamecheck
4> spfile
5> parameter_value_convert 'mecbs','sms','MECBS','SMS'
6> set service_names='SMS1'
7> set db_unique_name='SMS'
8> set db_file_name_convert='+DATA/mecbs','+DATA/sms'
9> set log_file_name_convert='+DATA/mecbs','+DATA/sms','+RECO/mecbs','+RECO/sms'
10> set control_files='+DATA','+RECO'
11> set instance_number='1'
12> set log_archive_config='dg_config=(MECBS,SMS)'
13> set log_archive_dest_1='location=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SMS'
14> set log_archive_dest_3='SERVICE=MECBS LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MECBS'
15> set standby_file_management='AUTO'
16> set diagnostic_dest='/u01/app/oracle'
17> set fal_server='MECBS1','MECBS2'
18> set cluster_database='TRUE'
19> set audit_file_dest='/u01/app/oracle/admin/SMS/adump'
20> set fal_client='SMS1'
21> set control_files='+DATA/sms/controlfile/control01','+RECO/sms/controlfile/control02'
22> set db_create_file_dest='+DATA'
23> set remote_listener='scan.cowelldg.com:1521';
Starting Duplicate Db at 29-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=289 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=430 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwMECBS1' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSMS1'   targetfile
'+DATA/mecbs/spfilemecbs.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileSMS1.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileSMS1.ora''";
}
executing Memory Script
Starting backup at 29-JUN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 instance=MECBS1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 instance=MECBS1 device type=DISK
Finished backup at 29-JUN-15
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileSMS1.ora''
contents of Memory Script:
{
   sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=SMSXDB)'' comment=
'''' scope=spfile";
   sql clone "alter system set service_names =
''SMS1'' comment=
'''' scope=spfile";
   sql clone "alter system set db_unique_name =
''SMS'' comment=
'''' scope=spfile";
   sql clone "alter system set db_file_name_convert =
''+DATA/mecbs'', ''+DATA/sms'' comment=
'''' scope=spfile";
   sql clone "alter system set log_file_name_convert =
''+DATA/mecbs'', ''+DATA/sms'', ''+RECO/mecbs'', ''+RECO/sms'' comment=
'''' scope=spfile";
   sql clone "alter system set control_files =
''+DATA'', ''+RECO'' comment=
'''' scope=spfile";
   sql clone "alter system set instance_number =
1 comment=
'''' scope=spfile";
   sql clone "alter system set log_archive_config =
''dg_config=(MECBS,SMS)'' comment=
'''' scope=spfile";
   sql clone "alter system set log_archive_dest_1 =
''location=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SMS'' comment=
'''' scope=spfile";
   sql clone "alter system set log_archive_dest_3 =
''SERVICE=MECBS LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MECBS'' comment=
'''' scope=spfile";
   sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
   sql clone "alter system set diagnostic_dest =
''/u01/app/oracle'' comment=
'''' scope=spfile";
   sql clone "alter system set fal_server =
''MECBS1'', ''MECBS2'' comment=
'''' scope=spfile";
   sql clone "alter system set cluster_database =
TRUE comment=
'''' scope=spfile";
   sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/SMS/adump'' comment=
'''' scope=spfile";
   sql clone "alter system set fal_client =
''SMS1'' comment=
'''' scope=spfile";
   sql clone "alter system set control_files =
''+DATA/sms/controlfile/control01'', ''+RECO/sms/controlfile/control02'' comment=
'''' scope=spfile";
   sql clone "alter system set db_create_file_dest =
''+DATA'' comment=
'''' scope=spfile";
   sql clone "alter system set remote_listener =
''scan.cowelldg.com:1521'' comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=SMSXDB)'' comment= '''' scope=spfile
sql statement: alter system set service_names = ''SMS1'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''SMS'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+DATA/mecbs'', ''+DATA/sms'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+DATA/mecbs'', ''+DATA/sms'', ''+RECO/mecbs'', ''+RECO/sms'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA'', ''+RECO'' comment= '''' scope=spfile
sql statement: alter system set instance_number = 1 comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(MECBS,SMS)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SMS'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_3 = ''SERVICE=MECBS LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MECBS'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/u01/app/oracle'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''MECBS1'', ''MECBS2'' comment= '''' scope=spfile
sql statement: alter system set cluster_database = TRUE comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/SMS/adump'' comment= '''' scope=spfile
sql statement: alter system set fal_client = ''SMS1'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA/sms/controlfile/control01'', ''+RECO/sms/controlfile/control02'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set remote_listener = ''scan.cowelldg.com:1521'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 484356096 bytes
Fixed Size                     2254464 bytes
Variable Size                264243584 bytes
Database Buffers             209715200 bytes
Redo Buffers                   8142848 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/sms/controlfile/control01';
restore clone controlfile to '+RECO/sms/controlfile/control02' from
'+DATA/sms/controlfile/control01';
}
executing Memory Script
Starting backup at 29-JUN-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_MECBS1.f_bak tag=TAG20150629T181210 RECID=60 STAMP=883678340
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 29-JUN-15
Starting restore at 29-JUN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=8 instance=SMS1 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=74 instance=SMS1 device type=DISK
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 29-JUN-15
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
"+data";
   switch clone tempfile all;
   set newname for datafile 1 to
"+data";
   set newname for datafile 2 to
"+data";
   set newname for datafile 3 to
"+data";
   set newname for datafile 4 to
"+data";
   set newname for datafile 5 to
"+data";
   set newname for datafile 6 to
"+data";
   set newname for datafile 7 to
"+DATA/sms/datafile/system01.dbf";
   set newname for datafile 8 to
"+DATA/sms/datafile/crm01.dbf";
   set newname for datafile 11 to
"+DATA/sms/datafile/aix_trans.dbf";
   backup as copy reuse
   datafile 1 auxiliary format
"+data"   datafile
2 auxiliary format
"+data"   datafile
3 auxiliary format
"+data"   datafile
4 auxiliary format
"+data"   datafile
5 auxiliary format
"+data"   datafile
6 auxiliary format
"+data"   datafile
7 auxiliary format
"+DATA/sms/datafile/system01.dbf"   datafile
8 auxiliary format
"+DATA/sms/datafile/crm01.dbf"   datafile
11 auxiliary format
"+DATA/sms/datafile/aix_trans.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data 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
Starting backup at 29-JUN-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/mecbs/datafile/system01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=+DATA/mecbs/datafile/undotbs1.258.862339391
output file name=+DATA/sms/datafile/undotbs1.266.884015877 tag=TAG20150629T181303
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:05:00
channel ORA_DISK_2: starting datafile copy
input datafile file number=00008 name=+DATA/mecbs/datafile/crm01.dbf
output file name=+DATA/sms/datafile/system01.dbf tag=TAG20150629T181303
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:09:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/mecbs/datafile/users.259.862339391
output file name=+DATA/sms/datafile/crm01.dbf tag=TAG20150629T181303
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:05:02
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=+DATA/mecbs/datafile/sysaux.257.862339391
output file name=+DATA/sms/datafile/users.269.884016419 tag=TAG20150629T181303
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/mecbs/datafile/system.256.862339387
output file name=+DATA/sms/datafile/sysaux.270.884016493 tag=TAG20150629T181303
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:03:47
channel ORA_DISK_2: starting datafile copy
input datafile file number=00005 name=+DATA/mecbs/datafile/example.264.862339751
output file name=+DATA/sms/datafile/system.271.884016657 tag=TAG20150629T181303
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:49
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/mecbs/datafile/undotbs2.265.862341013
output file name=+DATA/sms/datafile/example.272.884016721 tag=TAG20150629T181303
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:47
channel ORA_DISK_2: starting datafile copy
input datafile file number=00011 name=+DATA/mecbs/datafile/aix_trans.dbf
output file name=+DATA/sms/datafile/undotbs2.273.884016827 tag=TAG20150629T181303
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA/sms/datafile/aix_trans.dbf tag=TAG20150629T181303
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:07
Finished backup at 29-JUN-15
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=60 STAMP=884016843 file name=+DATA/sms/datafile/system.271.884016657
datafile 2 switched to datafile copy
input datafile copy RECID=61 STAMP=884016843 file name=+DATA/sms/datafile/sysaux.270.884016493
datafile 3 switched to datafile copy
input datafile copy RECID=62 STAMP=884016843 file name=+DATA/sms/datafile/undotbs1.266.884015877
datafile 4 switched to datafile copy
input datafile copy RECID=63 STAMP=884016843 file name=+DATA/sms/datafile/users.269.884016419
datafile 5 switched to datafile copy
input datafile copy RECID=64 STAMP=884016843 file name=+DATA/sms/datafile/example.272.884016721
datafile 6 switched to datafile copy
input datafile copy RECID=65 STAMP=884016843 file name=+DATA/sms/datafile/undotbs2.273.884016827
datafile 7 switched to datafile copy
input datafile copy RECID=66 STAMP=884016843 file name=+DATA/sms/datafile/system01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=67 STAMP=884016843 file name=+DATA/sms/datafile/crm01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=68 STAMP=884016843 file name=+DATA/sms/datafile/aix_trans.dbf
Finished Duplicate Db at 29-JUN-15

修改备库的参数文件:

MECBS2.__db_cache_size=213909504
SMS2.__db_cache_size=213909504
SMS1.__db_cache_size=268435456
SMS2.__java_pool_size=4194304
SMS1.__java_pool_size=4194304
SMS2.__large_pool_size=16777216
SMS1.__large_pool_size=16777216
SMS2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SMS1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SMS2.__pga_aggregate_target=163577856
SMS1.__pga_aggregate_target=163577856
SMS2.__sga_target=486539264
SMS1.__sga_target=486539264
SMS2.__shared_io_pool_size=0
SMS1.__shared_io_pool_size=0
SMS2.__shared_pool_size=184549376
SMS1.__shared_pool_size=184549376
SMS2.__streams_pool_size=0
SMS1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/SMS/adump'
*.audit_trail='DB'
*.cluster_database=TRUE
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=30
*.control_files='+DATA/sms/controlfile/control01','+RECO/sms/controlfile/control02'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/mecbs','+DATA/sms'
*.db_name='MECBS'
*.db_unique_name='SMS'
*.deferred_segment_creation=FALSE
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SMSXDB)'
*.fal_client='SMS1'
*.fal_client='SMS2'
*.fal_server='MECBS1','MECBS2'
SMS1.instance_number=1
SMS2.instance_number=2
*.instance_number=1
*.log_archive_config='dg_config=(MECBS,SMS)'
*.log_archive_dest_1='location=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SMS'
*.log_archive_dest_3='SERVICE=MECBS LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MECBS'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_3='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA/mecbs','+DATA/sms','+RECO/mecbs','+RECO/sms'
*.open_cursors=300
*.pga_aggregate_target=161480704
*.processes=150
*.remote_listener='scan.cowelldg.com:1521'
*.remote_login_passwordfile='exclusive'
*.service_names='SMS1'
*.sga_target=486539264
*.standby_file_management='AUTO'
SMS2.thread=2
SMS1.thread=1
SMS2.undo_tablespace='UNDOTBS2'
SMS1.undo_tablespace='UNDOTBS1'

SQL> startup mount pfile='/home/oracle/pfile_ok.ora';
ORACLE instance started.

Total System Global Area 484356096 bytes
Fixed Size      2254464 bytes
Variable Size    264243584 bytes
Database Buffers   209715200 bytes
Redo Buffers      8142848 bytes
Database mounted.
SQL> create spfile='+DATA/sms/spifleSMS.ora' from pfile='/home/oracle/pfile_ok.ora';

File created

[oracle@rac1 dbs]$ cat initSMS1.ora
spfile='+DATA/sms/spifleSMS.ora'
[oracle@rac2 dbs]$ cat initSMS2.ora
spfile='+DATA/sms/spifleSMS.ora'

把standby库添加到集群：

[oracle@rac2 dbs]$ srvctl add database -o /u01/app/oracle/product/11.2.0/db_1/ -p "+DATA/sms/spfileSMS.ora" -n MECBS -r physical_standby -s mount
PRKO-2082 : Missing mandatory option -d
[oracle@rac2 dbs]$ srvctl add database -d SMS -o /u01/app/oracle/product/11.2.0/db_1/ -p "+DATA/sms/spfileSMS.ora" -n MECBS -r physical_standby -s mount
PRCS-1007 : Server pool SMS already exists
PRCR-1086 : server pool ora.SMS is already registered
[oracle@rac2 dbs]$ srvctl add instance -d SMS -i SMS1 -n rac1
[oracle@rac2 dbs]$ srvctl add instance -d SMS -i SMS2 -n rac2
[oracle@rac2 dbs]$ srvctl status database -d SMS
Instance SMS1 is not running on node rac1
Instance SMS2 is not running on node rac2
[oracle@rac2 dbs]$ srvctl start database -d SMS
[oracle@rac2 dbs]$ srvctl status database -d SMS
Instance SMS1 is running on node rac1
Instance SMS2 is running on node rac2
ora.sms.db
1 ONLINE ONLINE rac1 Open
2 ONLINE ONLINE rac2 Open

在一个节点开启恢复进程：

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

Database altered.

SQL> select open_mode,name,instance_name from gv$database a ,gv$instance b where a.inst_id=b.inst_id;

OPEN_MODE      NAME      INSTANCE_NAME
-------------------- --------- ----------------
READ ONLY WITH APPLY MECBS     SMS1
READ ONLY WITH APPLY MECBS     SMS2

向AI问一下细节

oracle DG 主备库为RAC及一个主库对多个从库的实验环境搭建

猜你喜欢

最新资讯

相关推荐

相关标签