DataGuard 11gR2 for linux 6.4 --- dg broker 方式搭建

环境:

centos 6.4 + oracle 11.2
DG配置:
主库
主机名:dg1
数据库:test
unique name: ptest
ip:192.168.190.111

备库:
主机名:dg2
数据库:test
unique name: stest
ip:192.168.190.112

系统配置:

①配置主机名和hosts解析文件

主库和备库都要设置
主库:

[root@dg1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.190.111 dg1
192.168.190.112 dg2
[root@dg1 ~]# cat /etc/sysconfig/network
network          networking/      network-scripts/ 
[root@dg1~]# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=dg1

备库:

[root@dg2~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.190.111 dg1
192.168.190.112 dg2
[root@dg2 ~]# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=dg2

②selinux和ipstables

主库

[root@dg1 ~]# getenforce 
Permissive
[root@dg1~]# service iptables status
iptables:未运行防火墙。

备库:

[root@dg2~]# getenforce 
Permissive
[root@dg2 ~]# service iptables status
iptables:未运行防火墙。

③网络互通

[root@dg1~]# ping dg2
PING dgstandby (192.168.190.112) 56(84) bytes of data.
64 bytes from dgstandby (192.168.190.112): icmp_seq=1 ttl=64 time=0.645 ms
64 bytes from dgstandby (192.168.190.112): icmp_seq=2 ttl=64 time=0.535 ms
[root@dg2 ~]# ping dg1
PING dgmaster (192.168.190.111) 56(84) bytes of data.
64 bytes from dgmaster (192.168.190.111): icmp_seq=1 ttl=64 time=1.73 ms
64 bytes from dgmaster (192.168.190.111): icmp_seq=2 ttl=64 time=1.19 ms

环境变量(主库,备库都一样):

[oracle@dg1 ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/11g
export ORACLE_SID=test
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

主库配置:

1.设置主库db_unique_name:

SQL> alter system set db_unique_name=ptest scope=spfile;

2.设置主库为强制记录日志:

SQL> alter database force logging;

3.设置standy_file_management:

SQL> alter system set standby_file_management =’AUTO’;

4.创建standbylog:

SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/test/redo04.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/test/redo05.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/test/redo06.log' size 50m;

Database altered.

SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/test/redo07.log' size 50m;

Database altered.

5.开启DGbroker:

SQL> alter system set DG_BROKER_START=TRUE;
System altered.

6.设置db_file_name_convert ,log_file_name_convert参数

SQL> alter system set db_file_name_convert='/u01/oracle/oradata/test','/u01/oracle/oradata/test','/u01/oracle/fast_recovery_area/test','/u01/oracle/fast_recovery_area/test' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/oracle/oradata/test','/u01/oracle/oradata/test','/u01/oracle/fast_recovery_area/test','/u01/oracle/fast_recovery_area/test' scope=spfile;

6.开启归档

[oracle@dg1 test]$ mkdir archivelog
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter system set log_archive_dest_1='location=/u01/oracle/oradata/test/archivelog';
SQL> alter database open;
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/test/archivelog
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

7.生成pfile

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/oracle/11g/dbs/spfiletest
                                                 .ora
SQL> create pfile from spfile;

File created.

8.设置监听listener和tnsnames文件

[oracle@dg1 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/oracle/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = /u01/oracle/11g)
      (SID_NAME = test)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ptest)
      (ORACLE_HOME = /u01/oracle/11g)
      (SID_NAME = test)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ptest_DGMGRL)
      (ORACLE_HOME = /u01/oracle/11g)
      (SID_NAME = test)
    )
  )

ADR_BASE_LISTENER = /u01/oracle


[oracle@dg1 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

PTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

STEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

监听状态:

Service "ptest" has 2 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
  Instance "test", status READY, has 1 handler(s) for this service...
Service "ptest_DGMGRL" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully

备库配置

1.检查ORACLE_SID:

[oracle@dg2 ~] echo e c h o ORACLE_SID
test

2.拷贝主库必要文件及文件夹到备库

监听文件:

[oracle@dg2 ~]$ cd /u01/oracle/11g/network/admin/
[oracle@dg2 admin]$  scp -r 192.168.190.111:/u01/oracle/11g/network/admin/listener.ora ./
[oracle@dg2 admin]$ scp -r 192.168.190.111:/u01/oracle/11g/network/admin/tnsnames.ora ./

必要的文件及目录创建和copy:

[oracle@dg2 admin]$ cd /u01/oracle/11g/dbs/
[oracle@dg2 dbs]$  scp -r 192.168.190.111:/u01/oracle/11g/dbs/inittest.ora ./
[oracle@dg2 dbs]$  scp -r 192.168.190.111:/u01/oracle/11g/dbs/orapwtest ./

[oracle@dg2 dbs]$ cd /u01/oracle/
[oracle@dg2 oracle]$ scp -r 192.168.190.111:/u01/oracle/admin ./
[oracle@dg2 oracle]$ scp -r 192.168.190.111:/u01/oracle/fast_recovery_area ./
[oracle@dg2 oracle]$ mkdir -pv oradata/test/archivelog/

3.设置listener和tnsnames文件

[oracle@dg2 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/oracle/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.112)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = /u01/oracle/11g)
      (SID_NAME = test)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = stest)
      (ORACLE_HOME = /u01/oracle/11g)
      (SID_NAME = test)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = stest_DGMGRL)
      (ORACLE_HOME = /u01/oracle/11g)
      (SID_NAME = test)
    )
  )

ADR_BASE_LISTENER = /u01/oracle

[oracle@dg2 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

PTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.111)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

STEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )

4.修改参数文件inittest.ora里的参数db_unique_name 为stest,启动备库到nomount阶段,且启用监听

SQL>create spfile from pfile;
SQL> startup nomount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             427820072 bytes
Database Buffers          188743680 bytes
Redo Buffers                7507968 bytes
SQL> show parameter db_un

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STEST

[oracle@dg2 dbs]$ lsnrctl start
Services Summary...
Service "stest" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "stest_DGMGRL" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

5.rman 克隆数据库

[oracle@dg2 dbs]$ rman target sys/123456@ptest auxiliary sys/123456@stest nocatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 20 15:05:39 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2210486398)
using target database control file instead of recovery catalog
connected to auxiliary database: TEST (not mounted)

RMAN>  duplicate target database for standby from active database nofilenamecheck;
......
......
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=912352058 file name=/u01/oracle/oradata/test/users01.dbf
Finished Duplicate Db at 20-MAY-16

6.检查数据库状态

[oracle@dg2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 20 15:08:24 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

SQL> show parameter db_un

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      STEST

主库dg_broker操作:
主库:

[oracle@dg1 admin]$ dgmgrl /
DGMGRL> create configuration dg_test as primary database is ptest connect  identifier is ptest;
Configuration "dg_test" created with primary database "ptest"
DGMGRL> add database stest as connect identifier is stest maintained as physical;
Database "stest" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> enable database ptest;
Enabled.
DGMGRL> enable database stest;
Enabled.
DGMGRL> show configuration

Configuration - dg_test

  Protection Mode: MaxPerformance
  Databases:
    ptest - Primary database
    stest - Physical standby database
      Error: ORA-16797: database is not using a server parameter file

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

备库操作:

SQL> create spfile from pfile;

File created.
SQL> shutdown immediate
SQL> startup 
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TEST      STEST                          READ ONLY WITH APPLY PHYSICAL STANDBY

回到主库dgmgrl:
再查看状态

DGMGRL>  show configuration

Configuration - dg_test

  Protection Mode: MaxPerformance
  Databases:
    ptest - Primary database
    stest - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show database smobaov3 statusreport;
Object "smobaov3" was not found
DGMGRL> show database stest statusreport;
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                test    WARNING ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
                test    WARNING ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
                test    WARNING ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting

DGMGRL> EDIT DATABASE stest SET PROPERTY LogArchiveMinSucceedDest=1;   
Property "logarchiveminsucceeddest" updated
DGMGRL> EDIT DATABASE stest SET PROPERTY LogArchiveMaxProcesses=4;
Property "logarchivemaxprocesses" updated
DGMGRL> EDIT DATABASE stest SET PROPERTY  ArchiveLagTarget=0;
Property "archivelagtarget" updated
DGMGRL> show configuration

Configuration - dg_test

  Protection Mode: MaxPerformance
  Databases:
    ptest - Primary database
    stest - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

开始切换:

[oracle@dg1 admin]$ dgmgrl sys/123456@ptest    -----必须这样的登陆方式才可以
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - dg_test

  Protection Mode: MaxPerformance
  Databases:
    ptest - Primary database
    stest - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to stest
Performing switchover NOW, please wait...
Operation requires a connection to instance "test" on database "stest"
Connecting to instance "test"...
Connected.
New primary database "stest" is opening...
Operation requires startup of instance "test" on database "ptest"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "stest"
DGMGRL> show configuration

Configuration - dg_test

  Protection Mode: MaxPerformance
  Databases:
    stest - Primary database
    ptest - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to ptest
Performing switchover NOW, please wait...
Operation requires a connection to instance "test" on database "ptest"
Connecting to instance "test"...
Connected.
New primary database "ptest" is opening...
Operation requires startup of instance "test" on database "stest"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ptest"
DGMGRL> show configuration

Configuration - dg_test

  Protection Mode: MaxPerformance
  Databases:
    ptest - Primary database
    stest - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

补充

备库执行:
SQL> alter database open; ———–11g ADG特性 启动到 read-only状态
Database altered.

SQL> recover managed standby database using current logfile disconnect from session; ———-备库启用日志应用功能
Media recovery complete.

备库注册归档:
alter database register physical logfile ‘/home/oracle/log_1_7354_895934230.arc’;

备库查看DG进程
select process from v$managed_standby;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值