Dataguard架设记录

原创 2015年11月21日 11:11:14

    前段时间花了一周时间,再加2个熬夜到2点的结果,记录一下。

 

   

1.判断DataGuard是否安装

 select * from v$option where parameter = 'Oracle Data Guard';

2.网络配置

node1:

192.168.92.6(ole-11g02)-------192.168.92.5(ole-11g03)

3.监听配置

主库

[oracle@node1 ~]$ cd    /u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@node1 admin]$ cat  listener.ora

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

  )

 

 

 

 

[oracle@node1 admin]$  cat   tnsnames.ora

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

 

DG =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg)

    )

  )

 

 

 

 

 

 

 

 

 

 

 

备库

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@node1 admin]$ cat  listener.ora

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

  )

 

 

 

 

[oracle@node1 admin]$  cat   tnsnames.ora

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

 

DG =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg)

    )

  )

 

 

 

 

 

 

4.主库前期准备

设置强制写日志

SQL>  select  FORCE_LOGGING  from  v$database;

NO

SQL> alter  database  force  logging;

SQL> select  FORCE_LOGGING  from  v$database;

                

5.创建口令文件

orapwd  file=$ORACLE_HOME/dbs/orapwdb1  password=oracle  entries=5

6.修改主库初始化参数 

 创建主库pfile

 sql > create  pfile    from  spfile;

修改pfile

DB_UNIQUE_NAME=orcl

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'

LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_2= 'SERVICE=dg  LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dg'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=dg

FAL_CLIENT=orcl

STANDBY_FILE_MANAGEMENT=AUTO

 

 

 

Pfile 拷贝到备库上

scp –rp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora

node2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 

scp –rp  /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

   standby:/u01/app/oracle/product/11.2.0/db_1/dbs/

 

 

 

 

7.修改数据库运行在归档模式下

SHUTDOWN  IMMEDIATE;

STARTUP  MOUNT;

ALTER  DATABASE  ARCHIVELOG;

ALTER  DATABASE OPEN;

8.创建备份库需要的控制文件

创建控制文件

Shutdown immediate

STARTUP  MOUNT;

ALTER  DATABASE  CREATE  STANDBY  CONTROLFILE  AS  '/tmp/orcl.ctl';

ALTER  DATABASE OPEN;

创建主库pfile

Shutdown immediate

startup pfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'

sql>  create  spfile from  pfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'

;

(先把原来的干掉)

shutdown immediate;

startup

 

9.备份生产数据库

 scp -rp /u01/oradata/ORCL node2:/u01/oradata/

scp –rp  /u01/app/admin/orcl  node2:/u01/app/admin(记得在备库创建admin)

 

scp -rp  /u01/app/oracle/oradata node2: /u01/app/oradata

scp -rp  /u01/app/oracle/oradata     node2: /u01/app/oracle/oradata ()

10.修改备库pfile

DB_UNIQUE_NAME=orcl

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=

'LOCATION=/u01/app/archive

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=dg' 

LOG_ARCHIVE_DEST_2=

'SERVICE=orcl ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=orcl'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

FAL_SERVER=orcl

FAL_CLIENT=dg

STANDBY_FILE_MANAGEMENT=AUTO

11.复制控制文件

scp  -rp  /tmp/dg.ctl  node2:/u01/oradata/ORCL/controlfile/

scp  -rp  /tmp/dg.ctl  node2:/u01/flash_recovery_area/orcl/controlfile/

 

scp  -rp  /tmp/dg.ctl  node2:/u01/app/oracle/oradata/ORCL/controlfile

12.在备库上创建口令文件

orapwd  file=$ORACLE_HOME/dbs/orapwdb1  password=oracle  entries=5

13.在备库上创建spfile 

Shutdown immediate

startup pfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'

 

CREATE  SPFILE  FROM  PFILE;

14.启动物理备用数据库

STARTUP MOUNT;

15.Standby Redo Log

在两边都配置standby  redo  log

在主库查看日志组的数量和每个日志文件的大小

SQL> SELECT GROUP#, BYTES FROM V$LOG;

在备库库查看日志组的数量和每个日志文件的大小

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

创建日志组和redo log文件

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4('/oracle/dbs/slog1.rdo') SIZE 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/oracle/dbs/slog2.rdo') SIZE 50M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/oracle/dbs/slog3.rdo') SIZE 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 7('/oracle/dbs/slog4.rdo') SIZE 50M;

16.Start Redo Apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

查看哪些归档日志被APPLY了

在备库

SELECT  SEQUENCE#,APPLIED  FROM  V$ARCHIVED_LOG  ORDER  BY  SEQUENCE#;

在主库强制日志切换到当前的online redo log file.

ALTER  SYSTEM  ARCHIVE  LOG  CURRENT;

在备库查看新的被归档的redo data

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

在备库查看接收到的被应用的redo

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

查看数据库的角色

select database_role,protection_mode,protection_level from v$database;

 

 

 

---以上完成dg

 

l  主备库切换

 

1.查看主库的状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

2.将主库切换至备用模式

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH -

> SESSION SHUTDOWN;

 

3.关闭、装载主数据库

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

4.查看备库准备向主库模式切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO_PRIMARY

1 row selected

5.切换备库至主库模式

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

 

6.打开新的主数据库

 

SQL> ALTER DATABASE OPEN;

 

7. 在新的备库服务器上启动 REDO apply。

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -

> DISCONNECT FROM SESSION;

 

l  灾难恢复(failover)

 

Step 1 Flush any unsent redo from the primary database to the target standby

database

 

SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

 

Step 2 Verify that the standby database has the most recently archived redo log

file for each primary database redo thread.

 

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -

> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

 

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

 

Step 3 Identify and resolve any archived redo log gaps.

 

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

 

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

 

Step 4 Repeat Step 3 until all gaps are resolved.

 

Step 5 Stop Redo Apply.

Issue the following SQL statement on the target standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

Step 6 Finish applying all received redo data.

Issue the following SQL statement on the target standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

 

Step 7 Verify that the target standby database is ready to become a primary

database.

 

Step 8 Switch the physical standby database to the primary role.

Issue the following SQL statement on the target standby database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

 

Step 9 Open the new primary database.

SQL> ALTER DATABASE OPEN;

 

Step 10 Back up the new primary database.

Oracle recommends that a full backup be taken of the new primary database.

 

Step 11 Restart Redo Apply if it has stopped at any of the other physical standby

databases in your Data Guard configuration.

For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -

> DISCONNECT FROM SESSION;

附:

1.  启动到管理模式

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database recover managed standby database disconnect from session;

2.启动到只读方式

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database open read only;

3如果在管理恢复模式下到只读模式

SQL> recover managed standby database cancel;

SQL> alter database open read only;

 

DATAGUARD原理(二)--DG架构

从DG的架构图开始了解一下DG的各个组成部分: 简单来看由三部分组成:主库,备库,中间层(各种服务+日志、归档文件),中间层我们放到后面说,先看一下主库和备库: Primary Datab...
  • wujizhou
  • wujizhou
  • 2014年01月02日 12:17
  • 1074

ORACLE 11G DATAGUARD保护模式设置详解

先说说DG的三种保护模式 Maximum Protection: 最大保护模式,不允许数据丢失,所有事务必须完成主备库的日志写后才能提交,如果主备库之间网络异常,将会严重影响主库性能。 Maximum...
  • xhailing
  • xhailing
  • 2013年10月29日 16:45
  • 2466

最简单的11g Active DataGuard(ADG)搭建配置过程(项目步

最简单的11g Active DataGuard(ADG)搭建配置过程(项目步骤)一、环境介绍:    我在db01和db02两台Linux虚拟机上首先分别安装了一套数据库软件,在db01主机上创建了...
  • wuweilong
  • wuweilong
  • 2015年06月18日 16:42
  • 13553

Active Dataguard实践记录

  • 2017年08月16日 11:09
  • 137KB
  • 下载

Oracle10G dataguard实施操作记录

  • 2009年09月12日 19:44
  • 16KB
  • 下载

Oracle_RAC+ASM+DataGuard配置实验记录+常见问题

  • 2011年12月18日 11:58
  • 134KB
  • 下载

【Oracle RAC+DG实验】Oracle RAC+ASM+DataGuard配置实验记录+常见问题

【Oracle RAC+DG实验】Oracle RAC+ASM+DataGuard配置实验记录+常见问题 1、环境规划: ---RAC环境介绍(primary database)        ...
  • launch_225
  • launch_225
  • 2012年08月05日 22:09
  • 8645

dataguard配置记录及提示SQL

select name,SWITCHOVER_STATUS,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database; select SEQUE...
  • hzhvv
  • hzhvv
  • 2016年06月22日 17:01
  • 501

oracle11g dataguard 线上维护问题记录

1,问题汇总1: 主备切换问题 SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------...
  • mchdba
  • mchdba
  • 2016年03月20日 09:05
  • 2643

oracle 高可用 dataguard 单机到RAC复制与几乎无停顿全数据迁移

  • 2017年12月25日 19:53
  • 48KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Dataguard架设记录
举报原因:
原因补充:

(最多只允许输入30个字)