温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

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

发布时间:2020-08-09 20:18:13 来源:网络 阅读:2621 作者: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. 配置备库(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)

  2. 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)
        )
      )
    复制过程:

  3. RMAN> @duplicate.sql

  4. 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

  5. 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

  6. 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

  7. sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileSMS1.ora''

  8. 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

  9. sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=SMSXDB)'' comment= '''' scope=spfile

  10. sql statement: alter system set  service_names =  ''SMS1'' comment= '''' scope=spfile

  11. sql statement: alter system set  db_unique_name =  ''SMS'' comment= '''' scope=spfile

  12. sql statement: alter system set  db_file_name_convert =  ''+DATA/mecbs'', ''+DATA/sms'' comment= '''' scope=spfile

  13. sql statement: alter system set  log_file_name_convert =  ''+DATA/mecbs'', ''+DATA/sms'', ''+RECO/mecbs'', ''+RECO/sms'' comment= '''' scope=spfile

  14. sql statement: alter system set  control_files =  ''+DATA'', ''+RECO'' comment= '''' scope=spfile

  15. sql statement: alter system set  instance_number =  1 comment= '''' scope=spfile

  16. sql statement: alter system set  log_archive_config =  ''dg_config=(MECBS,SMS)'' comment= '''' scope=spfile

  17. sql statement: alter system set  log_archive_dest_1 =  ''location=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SMS'' comment= '''' scope=spfile

  18. 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

  19. sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

  20. sql statement: alter system set  diagnostic_dest =  ''/u01/app/oracle'' comment= '''' scope=spfile

  21. sql statement: alter system set  fal_server =  ''MECBS1'', ''MECBS2'' comment= '''' scope=spfile

  22. sql statement: alter system set  cluster_database =  TRUE comment= '''' scope=spfile

  23. sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/SMS/adump'' comment= '''' scope=spfile

  24. sql statement: alter system set  fal_client =  ''SMS1'' comment= '''' scope=spfile

  25. sql statement: alter system set  control_files =  ''+DATA/sms/controlfile/control01'', ''+RECO/sms/controlfile/control02'' comment= '''' scope=spfile

  26. sql statement: alter system set  db_create_file_dest =  ''+DATA'' comment= '''' scope=spfile

  27. sql statement: alter system set  remote_listener =  ''scan.cowelldg.com:1521'' comment= '''' scope=spfile

  28. Oracle instance shut down

  29. connected to auxiliary database (not started)
    Oracle instance started

  30. Total System Global Area     484356096 bytes

  31. Fixed Size                     2254464 bytes
    Variable Size                264243584 bytes
    Database Buffers             209715200 bytes
    Redo Buffers                   8142848 bytes

  32. 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

  33. 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

  34. 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

  35. channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
    channel ORA_AUX_DISK_1: copied control file copy
    Finished restore at 29-JUN-15

  36. contents of Memory Script:
    {
       sql clone 'alter database mount standby database';
    }
    executing Memory Script

  37. 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.

  38. 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

  39. executing command: SET NEWNAME

  40. renamed tempfile 1 to +data in control file

  41. executing command: SET NEWNAME

  42. executing command: SET NEWNAME

  43. executing command: SET NEWNAME

  44. executing command: SET NEWNAME

  45. executing command: SET NEWNAME

  46. executing command: SET NEWNAME

  47. executing command: SET NEWNAME

  48. executing command: SET NEWNAME

  49. executing command: SET NEWNAME

  50. 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

  51. sql statement: alter system archive log current

  52. contents of Memory Script:
    {
       switch clone datafile all;
    }
    executing Memory Script

  53. 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问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI