11g OCM备考5——DG搭建---参考

于 2018-05-28 09:37:45 发布
阅读量654 收藏 1
点赞数
分类专栏： oracle
本文详细介绍如何通过Oracle数据库搭建物理备库的过程，包括网络配置、参数调整、RMAN复制数据库、日志应用取消及快照数据库转换等内容。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1. 创建两台虚拟机
关闭防火墙

192.168.1.170 pri

192.168.1.180 sta

pri上建一数据库woods

2. 网络配置
建议用netmgr配置

pri端

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = woods.oracle.com)

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

      (SID_NAME = woods)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = woods_DGMGRL.oracle.com)

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

      (SID_NAME = woods)

    )

  )

 

LISTENER =

  (DESCRIPTION =

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

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

 

WOODS =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = woods.oracle.com)

    )

  )

 

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = standby.oracle.com)

    )

  )

 
sta端

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = standby.oracle.com)

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

      (SID_NAME = standby)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = standby_DGMGRL.oracle.com)

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

      (SID_NAME = standby)

    )

  )

 

LISTENER =

  (DESCRIPTION =

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

  )

 

ADR_BASE_LISTENER = /u01/app/oracle

 

tnsnames.ora两端一模一样



3. 修改pri端的参数和添加standby log

alter database force logging;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(woods,standby)';

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=woods'

alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/woods/' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/woods/' scope=spfile;

alter system set STANDBY_FILE_MANAGEMENT=AUTO;

切成归档模式：

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;


添加standby log,要比redo log多一组：

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby01.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby02.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby03.log' size 50m;

alter database add standby logfile '/u01/app/oracle/oradata/woods/standby04.log' size 50m;

 
4. 传输密码文件和初始化参数文件到sta端

create pfile from spfile;

scp orapwwoods oracle@sta:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstandby

scp initwoods.ora oracle@sta:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initstandby.ora

5. sta端修改参数，并创建相应路径

修改initstandby.ora，相应路径要创建

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/standby/control01.ctl'

*.db_block_size=8192

*.db_domain='oracle.com'

*.db_file_name_convert='/u01/app/oracle/oradata/woods/','/u01/app/oracle/oradata/standby/'

*.db_name='woods'

*.db_unique_name='standby'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'

*.log_archive_config='DG_CONFIG=(woods,standby)'

.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.log_archive_dest_2='SERVICE=woods async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=woods'

*.log_file_name_convert='/u01/app/oracle/oradata/woods/','/u01/app/oracle/oradata/standby/'

*.memory_target=930086912

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'


6. RMAN复制数据库

standby端：

create spfile from pfile;

startup nomount;

woods端：

rman target sys/oracle@woods auxiliary sys/oracl@standby

duplicate for standby from active database;

(duplicate target database for standby from active database;)

 

duplicate完成后，standby库就是mount状态了

SQL> select OPEN_MODE from v$database;

OPEN_MODE

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

MOUNTED


alter database recover managed standby database disconnect from session;

（取消日志应用，用命令：alter database recover managed standby database cancel;）

 

到这里就算基本配置完成了，下一章介绍broker和switchover等。



1. 打开备库，能够提供实时查询


sta端 

alter database open;

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY WITH APPLY

 

 

DGMGRL> show database verbose standby;

 

Database - standby

 

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: ON

  Instance(s):

    standby

 

2. 启用块修改跟踪

alter database enable block change tracking using file '/home/oracle/blk_chg.tra';

 

SQL> select status from v$block_change_tracking;

STATUS

----------

ENABLED

 

SQL> select filename from v$block_change_tracking;

FILENAME

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

/home/oracle/blk_chg.tra

 

3. convert 使用

将standby转换成快照数据库，可以用来测试用

DGMGRL> CONVERT DATABASE standby to SNAPSHOT STANDBY;

GMGRL> show configuration;

 

Configuration - c1

 

  Protection Mode: MaxPerformance

  Databases:

    woods   - Primary database

    standby - Snapshot standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS

 

CONVERT DATABASE standby to PHYSICAL STANDBY;

 

4. 主备库切换

switchover

DGMGRL> SWITCHOVER TO standby;

 

DGMGRL> show configuration;

 

Configuration - c1

 

  Protection Mode: MaxPerformance

  Databases:

    standby - Primary database

    woods   - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS

 

SWITCHOVER TO woods;

 

 

5. 设置归档日志保留策略


rman target /

CONFIGURE ARCHIVELOG DELETION POLICY TO shipped to standby;

会有这个报错：RMAN-08591: WARNING: invalid archived log deletion policy

alter system set "_log_deletion_policy"=ALL scope=spfile ;

重启一下主库，再重新配置rman参数，就不会报错了

 

6. 配置Fast-start Failover

 

（1）主备库都启动快速闪回

alter database flashback on;

（备库在应用日志，启动不了，可以先取消日志应用，启动闪回后再开启日志应用：

alter database recover managed standby database cancel;

alter database flashback on;

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

 

（2）EDIT DATABASE woods SET PROPERTY FastStartFailoverTarget='standby';

（3）sta端

     [oracle@sta ~]$ dgmgrl sys/oracle

     DGMGRL> start observer;

（4）DGMGRL> EDIT DATABASE woods SET PROPERTY FastStartFailoverTarget='standby';

DGMGRL> EDIT DATABASE woods SET PROPERTY LogXptMode='SYNC';

DGMGRL> EDIT DATABASE standby SET PROPERTY LogXptMode='SYNC';

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

（5）启动

DGMGRL> ENABLE FAST_START FAILOVER;

Enabled.

DGMGRL> show configuration;

 

Configuration - c1

 

  Protection Mode: MaxAvailability

  Databases:

    woods   - Primary database

    standby - (*) Physical standby database

 

Fast-Start Failover: ENABLED

 

Configuration Status:

SUCCESS




评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值