【DataGuard安装】Oracle单实例到RAC用duplicate active database做DataGuard之二

最新推荐文章于 2021-04-29 15:48:47 发布

bamuta

最新推荐文章于 2021-04-29 15:48:47 发布

阅读量2k

点赞数

本文链接：https://blog.csdn.net/bamuta/article/details/41694531

版权

Oracle 同时被 3 个专栏收录

173 篇文章 0 订阅

订阅专栏

Oracle install

42 篇文章 0 订阅

订阅专栏

Oracle DataGuard

13 篇文章 0 订阅

订阅专栏

说明

本文较为详细的记录了操作过程，在做完DG后，进行了2次switchover,最后以ActiveData Guard的方式打开了备库。

其他文章：

下面一篇同一台机器上使用duplicate创建DG的简要说明

使用duplicate fromactive database创建Active Data Guard -主要过程说明

下面一篇和本文类似，方法略有不同，记录的较为粗略。

Oracle单实例到RAC用duplicate active database做DataGuard之一

1.源端：（11g单实例）

IP: 192.168.114.176

Hostname: dgh1

db_unique_name: tandb

2.目标端：（11gRAC）

IP: 192.168.20.241, 192.168,20.242

VIP: 192.168.20.243, 192.168,20.244

Scan-ip: 192.168.20.245

Hostname: racnode1,racnode2

db_unique_name: dgdb

3.此次DG的创建通过duplicatetarget database for standby from active database创建DG

4.数据库版本，两端都是11.2.0.4

配置主库

源端参数：

如果使用了MEMORY_TARGET ，必须大于752M

源端必须为归档模式。

SQL>  ALTER DATABASE FORCE LOGGING;
查看主库当前是否为force logging：
SQL> select force_logging fromv$database;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(tandb,dgdb)' scope=both;
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1= 'LOCATION=/u02/arch1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tandb';
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=dgdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)db_unique_name=dgdb' scope=both;

SQL> alter system set log_archive_format= '%t_%s_%r.arc' scope=spfile;
SQL> alter system set standby_file_management=auto;

--standby_file_management=auto:主库有表空间变动时，能够自动同步到备库。

重启数据库

静态监听

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = dgh1.localdomain)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
SID_LIST_LISTENER=
 (SID_LIST=
    (SID_DESC=
       (GLOBAL_DBNAME=tandb)
       (SID_NAME=tandb)
       (ORACLE_HOME=/u02/oracle/product/11.2.0/dbhome_1)
     )
   )

TNSNAMES

TANDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST =dgh1.localdomain)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = tandb)
    )
  )
 
dgdb =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(host = 192.168.20.243)(PORT = 1522))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (service_name = dgdb)
    )
  )

--暂时只连接到节点1

LOG

alter database add logfile thread 2 group 4('/u02/oracle/oradata/tandb/redo04.log') size 50m;
alter database add logfile thread 2 group 5('/u02/oracle/oradata/tandb/redo05.log') size 50m;
alter database add logfile thread 2 group 6('/u02/oracle/oradata/tandb/redo06.log') size 50m;
SQL> selectgroup#,thread#,bytes/1024/1024 MB ,members from V$log;
 
   GROUP#    THREAD#         MB   MEMBERS
---------- ---------- ---------- ----------
        1          1         50          1
        2          1         50          1
        3          1         50          1
        4          2         50          1
        5          2        50          1
        6          2         50          1

STANDBYLOG

alter database add standby logfile thread 1group 7 ('/u02/oracle/oradata/tandb/s_redo07.log') size 50m;
alter database add standby logfile thread 1group 8 ('/u02/oracle/oradata/tandb/s_redo08.log') size 50m;
alter database add standby logfile thread 1group 9 ('/u02/oracle/oradata/tandb/s_redo09.log') size 50m;
alter database add standby logfile thread 1group 10 ('/u02/oracle/oradata/tandb/s_redo10.log') size 50m;
alter database add standby logfile thread 2group 11 ('/u02/oracle/oradata/tandb/s_redo11.log') size 50m;
alter database add standby logfile thread 2group 12 ('/u02/oracle/oradata/tandb/s_redo12.log') size 50m;
alter database add standby logfile thread 2group 13 ('/u02/oracle/oradata/tandb/s_redo13.log') size 50m;
alter database add standby logfile thread 2group 14 ('/u02/oracle/oradata/tandb/s_redo14.log') size 50m;
SQL> selectgroup#,thread#,bytes/1024/1024 MB,status,used from V$standby_log;
 
   GROUP#    THREAD#         MB STATUS           USED
---------- ---------- ---------- --------------------
        7          1         50 UNASSIGNED          0
        8          1         50 UNASSIGNED          0
        9          1         50 UNASSIGNED          0
       10          1         50 UNASSIGNED          0
       11          2         50 UNASSIGNED          0
       12          2         50 UNASSIGNED          0
       13          2         50 UNASSIGNED          0
       14          2         50 UNASSIGNED          0

UNDOTABLESPACE

SQL> create undo tablespace undotbs2datafile '/u02/oracle/oradata/tandb/undotbs02.dbf' size 50m;
 
SQL> select SWITCHOVER_STATUS,PROTECTION_MODE,DATABASE_ROLE,open_mode from V$database;
 
SWITCHOVER_STATUS    PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ------------------------------------ --------------------
FAILED DESTINATION   MAXIMUM PERFORMANCE  PRIMARY         READ WRITE

目标端备库配置

创建密码文件

将密码文件从源端拷贝到目标端的节点1，节点2

$ scp orapwtandb oracle@192.168.20.241:/u01/apps/oracle/product/11.2.0/dbhome_1/dbs/

$ scp orapwtandb oracle@192.168.20.242:/u01/apps/oracle/product/11.2.0/dbhome_1/dbs/

在各自节点修改名称

$ mv orapwtandb orapwtandb1

$ mv orapwtandb orapwtandb2

增加监听

Grid用户下，listener.ora文件中新增以下内容

节点1：
LISTENER2 =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(PORT = 1522))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
 
SID_LIST_LISTENER2=
 (SID_LIST=
    (SID_DESC=
       (GLOBAL_DBNAME=dgdb)
       (SID_NAME=tandb1)
       (ORACLE_HOME=/u01/apps/oracle/product/11.2.0/dbhome_1)
     )
   )
节点2：
LISTENER2 =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(PORT = 1522))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_LISTENER2=
 (SID_LIST=
    (SID_DESC=
       (GLOBAL_DBNAME=dgdb)
       (SID_NAME=tandb2)
       (ORACLE_HOME=/u01/apps/oracle/product/11.2.0/dbhome_1)
     )
   )

tnsnames

oracle用户下，2个节点都新增如下内容。

TANDB =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.114.176)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = tandb)
    )
  )
 
dgdb =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(host = 192.168.20.245)(PORT = 1522))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (service_name = dgdb)
    )
  )

在节点1上创建参数文件

[oracle@racnode1 dbs]$ more inittandb1.ora

db_name=tandb
db_unique_name=dgdb
CONTROL_FILES='+DATA'
db_create_file_dest='+DATA'
log_archive_config='DG_CONFIG=(tandb,dgdb)'
db_file_name_convert='/u02/oracle/oradata/tandb/','+DATA/','tandb','dgdb'
log_file_name_convert='/u02/oracle/oradata/tandb/','+DATA/'
log_archive_format= %t_%s_%r.arc
log_archive_dest_1='location=+DATAvalid_for=(all_logfiles,all_roles) db_unique_name=dgdb'
log_archive_dest_2='service=tandb asyncvalid_for=(online_logfiles,primary_role) db_unique_name=tandb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
cluster_database=true
cluster_database_instances=2
compatible=11.2.0.4.0
tandb1.instance_number=1
tandb2.instance_number=2

创建standby库

节点1启动到nomount

SQL> startup nomount;

[oracle@dgh1 ~]$ rman targetsys/oracle@tandb auxiliary sys/oracle@dgdb

RMAN> duplicate target database forstandby from active database;

创建共享的spfile文件

SQL> create pfile from spfile;

SQL> create spfile=' +DATA/DGDB/spfile.ora'from pfile;

ASMCMD> pwd

+DATA/DGDB/PARAMETERFILE

ASMCMD> ls

spfile.324.865357457

ASMCMD> mkalias spfile.324.865357457../spfiletandb.ora

注册database到srvctl

SQL> shutdown immediate

[oracle@racnode1 ~]$ srvctl add database -ddgdb -o /u01/apps/oracle/product/11.2.0/dbhome_1/ -p +DATA/DGDB/spfiletandb.ora

[oracle@racnode1 ~]$ srvctl add instance -ddgdb -i tandb1 -n racnode1

[oracle@racnode1 ~]$ srvctl add instance -ddgdb -i tandb2 -n racnode2

用sqlplus启动实例1，然后修改以下参数：

SQL> alter system set instance_number=1sid='tandb1' scope=spfile;
SQL> alter system set thread=1sid='tandb1' scope=spfile;
SQL>alter system setundo_tablespace='UNDOTBS1' sid='tandb1' scope=spfile;
SQL> alter system set instance_number=2sid='tandb2' scope=spfile;
SQL> alter system set thread=2sid='tandb2' scope=spfile;
SQL>alter system setundo_tablespace='UNDOTBS2' sid='tandb2' scope=spfile;

重启数据库

[oracle@racnode2 ~]$ srvctl stop database-d dgdb
[oracle@racnode2 ~]$ srvctl start database-d dgdb
[oracle@racnode2 ~]$ srvctl status database-d dgdb
Instance tandb1 is running on node racnode1
Instance tandb2 is running on node racnode2

启动归档应用归档

SQL> alter database recover managedstandby database using current logfile disconnect from session;
 
SQL> select open_mode from V$database;
 
OPEN_MODE
--------------------
READ ONLY WITH APPLY
 
SQL> select sequence#,applied fromV$archived_log   order by sequence#;
 
SEQUENCE# APPLIED
---------- ---------
        5 YES
        6 YES
        7 YES
        8 IN-MEMORY

SWITCHOVER

第一次切换

切换间测试：切换前，主库tandb创建一个，插入一条数据

SQL> insert into t1 values(11);
SQL> commit;
SQL> select * from t1;
      XID
----------
       11

将主库tandb变为备库：

SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
 
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSIONSHUTDOWN;
SQL> shutdown abort
SQL> startup mount;

将备库dgdb切换为主库

--节点1上操作：
SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> ALTER DATABASE OPEN;
--节点2也open
SQL> alter database open;
SQL> selectSWITCHOVER_STATUS,PROTECTION_MODE, DATABASE_ROLE,open_mode fromV$database;   
 
SWITCHOVER_STATUS    PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ------------------------------------ --------------------
SESSIONS ACTIVE      MAXIMUM PERFORMANCE  PRIMARY          READ WRITE
[grid@racnode1 ~]$ srvctl status database-d dgdb
Instance tandb1 is running on node racnode1
Instance tandb2 is running on node racnode2

在新的备库tandb上启用redo apply

SQL> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> selectSWITCHOVER_STATUS,PROTECTION_MODE, DATABASE_ROLE,open_mode from V$database;
 
SWITCHOVER_STATUS    PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ------------------------------------ --------------------
NOT ALLOWED          MAXIMUM PERFORMANCE  PHYSICAL STANDBY MOUNTED

切换后新的主库dgdb查询，并插入数据

首先在节点1上插入数据：
SQL> select * from t1;
 
      XID
----------
       11
SQL> insert into t1 values(12);
SQL> commit;
 
节点2上也插入1条数据：
 
SQL> insert into t1 values(13);
SQL> commit;
SQL> select * from t1;
 
      XID
----------
       13
       12
       11

第二次切换

将主库dgdb变为备库：

下面的操作在节点1上进行。

SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
 
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVERTO PHYSICAL STANDBY;
SQL>shutdown abort
SQL> startup mount;
节点2也启至mount状态
SQL> startup mount;

将备库tandb切换为主库

SQL> conn /as sysdba
Connected.
SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVERTO PRIMARY;
SQL> ALTER DATABASE OPEN;
SQL> selectSWITCHOVER_STATUS,PROTECTION_MODE, DATABASE_ROLE,open_mode from V$database;
 
SWITCHOVER_STATUS    PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ------------------------------------ --------------------
TO STANDBY           MAXIMUM PERFORMANCE  PRIMARY          READ WRITE

在备库dgdb上启用redo apply

先将2个节点都OPEN，做成active data guard

SQL> alter database open;
SQL> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> selectSWITCHOVER_STATUS,PROTECTION_MODE, DATABASE_ROLE,open_mode from V$database;
SQL> select SWITCHOVER_STATUS,PROTECTION_MODE, DATABASE_ROLE,open_mode fromV$database;
 
SWITCHOVER_STATUS    PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ------------------------------------ --------------------
NOT ALLOWED          MAXIMUM PERFORMANCE  PHYSICAL STANDBY READ ONLY WITH APPLY

测试ACTIVE DATAGUARD

主库以下操作：

SQL> select * from t1;
 
      XID
----------
       13
       12
       11
 
SQL> insert into t1 values(14);
Commit complete.
备库查询
SQL> select * from t1
      XID
----------
       13
       12
       11
       14