ITPub博客

Oracle DataGuard 搭建

原创 Oracle 作者:恩强Boy 时间:2018-07-12 09:33:35 0 删除 编辑


一、准备工作. 

1.规划. 

2.关闭防火墙. 

3.禁用selinux防火墙. 

二、开启归档模式(主备库). 

1、创建归档目录. 

2、开启归档模式. 

三、强制主库为force logging模式(主库). 

四、HOST文件配置(主备库). 

1、主备库监听配置. 

2、修改tnsnames.ora文件(主备库). 

六、修改参数文件(主备库). 

1、主库修改参数. 

2、备库修改参数. 

七、复制数据库. 

1、备库开启到nomount模式. 

2、RMAN进行复制(主库). 

八、查询主备库角色. 

1、主库查询. 

2、备库查询. 

3.参数设置检查.

九、备库添加standby 日志组(备库). 

1、查看数据库日志组. 

2、新建备库日志组. 

3、查看日志组状态. 

十、备库应用日志. 

十一、验证数据库操作. 

1、在主库创建用户. 

2、在备库查看. 

十二、主备库切换操作验证(switchover). 

1. 主库执行切换.

2. 备库切换成主库. 

3.新备库开启实时应用(A机). 

4.切换回最初的状态. 

 

 

 

一、准备工作

 

1.规划

 

 

主库

备库

ip

192.168.131.100

192.168.131.101

instance_name

orcl1

orcl2

service_names

orcl1

orcl1

db_name(nomount修改)

orcl1

orcl1

db_unique_name

db01

db02

HostName

primary

standby

OS版本

Oracle linux 6.4

Oracle linux 6.4

DB版本

11.2.0.4

11.2.0.4

 

2.关闭防火墙

      service iptables stop

      chkconfig iptables off

 

3.禁用selinux防火墙

 

      vi /etc/selinux/config

selinux=disabled

 

二、开启归档模式(主备库)

 

1、创建归档目录

[oracle@primary ~]# mkdir -p /u01/app/oracle/archivelog

2、开启归档模式

 

(1)数据库到mount状态开启归档模式

 

[root@primary ~]# su - oracle

[oracle@primary ~]$ sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  776646656 bytes

Fixed Size                  2257272 bytes

Variable Size             507514504 bytes

Database Buffers          264241152 bytes

Redo Buffers                2633728 bytes

Database mounted.

 

(2)设置主库归档目录

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog';

(3)开启归档模式

SQL> alter database archivelog;

Database altered.

(4)查看归档设置

SQL> archive log list;

Database log mode        Archive Mode

Automatic archival           Enabled

Archive destination          /u01/app/oracle/archivelog

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence         4

 

(5) 开启数据库

SQL> alter database open;

Database altered.

 

三、强制主库为force logging模式(主库)

 

SQL> select force_logging from v$database;

 

FOR

---

NO

 

SQL> alter database force logging;

Database altered.

 

SQL> select force_logging from v$database;

 

FOR

---

YES

 

四、HOST文件配置(主备库)

 

Root用户(主备库相同)

[root@primary ~]# vi /etc/hosts

 

127.0.0.1   localhost localhost.localdomain

 

#Primary database IP

192.168.131.100 primary

#Standby database IP

      192.168.131.101 standby

 

 

五、配置lintener & tnsnames(主备库)

Oracle用户

 

1、主备库监听配置

(1)主库监听配置

 

[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/listener.ora

(添加以下内容)

SID_LIST_LISTENER =

   (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = db01)

        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

          (SID_NAME = orcl1)

        )

)

 

(2)备库监听配置

 

 [oracle@primary ~]$ vi $ORACLE_HOME/network/admin/listener.ora

(添加以下内容)

SID_LIST_LISTENER =

   (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = db02)

        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

          (SID_NAME = orcl2)

        )

)

 

2、修改tnsnames.ora文件(主备库)

 

(1)主库修改

[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

 

db01 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)     ##此处service应和上面SID_name值相等

    )

  )

 

db02 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)

       (UR=A)

    )

  )

 

(2)备库修改

[oracle@primary ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

 

db01 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)

    )

  )

 

db02 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl1)

       (UR=A)

    )

  )

 

(3)主备库检测(主备库)

[oracle@primary admin]$ tnsping db01

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-JUL-2018 08:48:56

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1)))

OK (10 msec)

 

[oracle@primary admin]$ tnsping db02

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-JUL-2018 08:49:01

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl1) (UR=A)))

OK (10 msec)

 

 

六、修改参数文件(主备库)

 

1、主库修改参数

 

(1)生成参数文件

SQL> create pfile from spfile;

File created.

 

(2)修改参数文件

[oracle@primary ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@primary dbs]$ vi initorcl1.ora

 

添加以下内容:

db_unique_name=db01

log_archive_config='dg_config=(db01,db02)'

log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=db01'

log_archive_dest_2='service=db02 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=db02'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

 

db_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'

log_file_name_convert='/u01/app/oracle/oradata/orcl2','/u01/app/oracle/oradata/orcl1'

fal_server=db02        --tnsnames连接串名

fal_client=db01

standby_file_management=auto                   

 

(3)生成spfile(shutdown状态)

create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora';


(4)开启数据库

startup(使新参数生效)


2、备库修改参数

 

(1)生成参数文件

SQL> create pfile from spfile;

File created.

 

(2)修改参数文件

[oracle@primary ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@primary dbs]$ vi initorcl2.ora

 

添加以下内容:

db_unique_name=db02

log_archive_config='dg_config=(db01,db02)'

log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=db02'

log_archive_dest_2='service=db01 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=db01'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

 

db_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'

log_file_name_convert='/u01/app/oracle/oradata/orcl1','/u01/app/oracle/oradata/orcl2'

fal_server=db01

fal_client=db02

standby_file_management=auto                    // 如果要修改备库日志文件的大小,需要将此处                                                                            auto修改成manual

 

****************

(3)生成spfile(shutdown状态)

create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl2.ora';


(4)开启数据库

startup(使新参数生效)


七、复制数据库

 

1、备库开启到nomount模式

 

SQL> shutdown immediate;

SQL> startup nomount;

 

2RMAN进行复制(主库)

 

[oracle@primary ~]$ rman target sys/oracle@db01 auxiliary sys/oracle@db02

 RMAN>duplicate target database for standby nofilenamecheck from active database;

 

此时,已经完成了DataGuard搭建部分!

 

八、查询主备库角色

 

1、主库查询

SQL> col db_unique_name for a15

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME  OPEN_MODE      DATABASE_ROLE       SWITCHOVER_STATUS

---------------       --------------------  ----------------          --------------------

db01                  READ WRITE          PRIMARY             SESSIONS ACTIVE

2、备库查询

SQL> col db_unique_name for a15

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME   OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

---------------        --------------------       ----------------         --------------------

db02            READ ONLY WITH APPLY     PHYSICAL STANDBY   NOT ALLOWED

 

3.参数设置检查

 

SQL> col name for a25

SQL> col value for a30

SQL> select a.name, a.value

  2  from v$parameter a

  3  where a.name like '%file_name_convert'

  4  or    a.name like '%fal%'

  5  or    a.name like 'standby_file%';

 

NAME                      VALUE

------------------------- ------------------------------

db_file_name_convert      /u01/app/oracle/oradata/orcl2,

                            /u01/app/oracle/oradata/orcl1

log_file_name_convert     /u01/app/oracle/oradata/orcl2,

                            /u01/app/oracle/oradata/orcl1

fal_client                   db01

fal_server                  db02

standby_file_management   auto

 

九、备库添加standby 日志组(备库)

1、查看数据库日志组

查看数据库的日志组个数与大小,因为我们创建 standby 日志组的个数是原日志

组个数+1 再与 thread 的积((1)*3),size 不能小于原日志文件的大小。

SQL> select group#,thread#,bytes/1024/1024 M,status from v$log;

 

   GROUP#    THREAD#          M STATUS

---------- ---------- ---------- ----------------

         1          1         50 UNUSED

         2          1         50 CLEARING

         3          1         50 CURRENT

 

SQL> select member from v$logfile;

 

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl2/redo03.log

/u01/app/oracle/oradata/orcl2/redo02.log

/u01/app/oracle/oradata/orcl2/redo01.log 

 

2、新建备库日志组

 

SQL> alter database add standby logfile thread 1 group 4

  2  ('/u01/app/oracle/oradata/orcl2/redo04.log') size 50M;

Database altered.

 

SQL> alter database add standby logfile thread 1 group 5

  2  ('/u01/app/oracle/oradata/orcl2/redo05.log') size 50M;

Database altered.

 

SQL> alter database add standby logfile thread 1 group 6

  2  ('/u01/app/oracle/oradata/orcl2/redo06.log') size 50M;

Database altered.

 

SQL> alter database add standby logfile thread 1 group 7

  2  ('/u01/app/oracle/oradata/orcl2/redo07.log') size 50M;

Database altered. 

 

3、查看日志组状态

 

 SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS  TYPE    MEMBER

---------- ------- ------- --------------------------------------------------

         3         ONLINE  /u01/app/oracle/oradata/orcl2/redo03.log

         2         ONLINE  /u01/app/oracle/oradata/orcl2/redo02.log

         1         ONLINE  /u01/app/oracle/oradata/orcl2/redo01.log

         4         STANDBY /u01/app/oracle/oradata/orcl2/redo04.log

         5         STANDBY /u01/app/oracle/oradata/orcl2/redo05.log

         6         STANDBY /u01/app/oracle/oradata/orcl2/redo06.log

         7         STANDBY /u01/app/oracle/oradata/orcl2/redo07.log

 

十、备库应用日志

 

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

 

十一、验证数据库操作

 

1、在主库创建用户

 

SQL> create user test identified by test;

User created.

 

SQL> alter system switch logfile;

System altered.

2、在备库查看

 

SQL> select username from dba_users where username='TEST';

 

USERNAME

------------------------------

TEST

 

十二、主备库切换操作验证switchover

 

1. 主库执行切换

 

(1) 查看主库状态

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

db01            READ WRITE           PRIMARY          SESSIONS ACTIVE

 

(2)主库执行切换命令

SQL> alter database commit to switchover to physical standby with session shutdown wait;

Database altered.

(2)重启主库角色变为备库(mount状态)

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  776646656 bytes

Fixed Size                  2257272 bytes

Variable Size             507514504 bytes

Database Buffers          264241152 bytes

Redo Buffers                2633728 bytes

Database mounted.

 

(4)查看主库状态

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

db01            MOUNTED              PHYSICAL STANDBY    RECOVERY NEEDED 

 

2. 备库切换成主库

 

(1)查看备库状态

 

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME   OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

---------------      --------------------       ----------------       --------------------

db02            READ ONLY WITH APPLY   PHYSICAL STANDBY   SESSIONS ACTIVE

 

(2)将备库切换成主库

 

SQL> alter database commit to switchover to primary with session shutdown wait;

Database altered.

(3)查看备库状态

 

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

db02            MOUNTED              PRIMARY          NOT ALLOWED

(4)开启备库

 

SQL> alter database open;

 

Database altered.

3.新备库开启实时应用(A机)

(做这一步需要给A机添加standby redo logs[同第九章])

 

 (1)查看备库状态

 

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

db01            MOUNTED              PHYSICAL STANDBY   RECOVERY NEEDED

 

(2)新备库应用日志

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

 

(3)新备库取消应用日志

SQL>alter database recover managed standby database cancel;

SQL> alter database open;

 

4.切换回最初的状态

 

(1)新主库操作(B机)

SQL> alter database commit to switchover to physical standby with session shutdown;

 

Database altered.

 

SQL> startup mount;

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

 

(退了重新进)

SQL> exit

Disconnected

[oracle@standby ~]$ sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  776646656 bytes

Fixed Size                  2257272 bytes

Variable Size             507514504 bytes

Database Buffers          264241152 bytes

Redo Buffers                2633728 bytes

Database mounted.

 

(2)新备库操作(A机)

SQL> ALTER  DATABASE RECOVER MANAGED STANDBY  DATABASE DISCONNECT FROM SESSION;

 

(因为没有应用日志,如果直接切A机会导致以下错误)

ORA-16139: media recovery required

 

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

db01                    READ WRITE           PRIMARY          RESOLVABLE GAP

 

(3)B机操作

 

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

Database altered.

 

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

 

DB_UNIQUE_NAME      OPEN_MODE      DATABASE_ROLE    SWITCHOVER_STATUS

-------------------- -------------------- ---------------- --------------------

db02                 MOUNTED            PHYSICAL STANDBY  NOT ALLOWED

 

 

此时,已经切换回最初的状态!

 

 

------------- end ------------

请登录后发表评论 登录
全部评论

注册时间:2018-04-03

  • 博文量
    32
  • 访问量
    7176