Oracle 11g Data Guard: How to start and stop Redo Apply

25-04-28 1

这篇文章主要围绕Oracle11gDataGuard:HowtostartandstopRedoApply展开，旨在为您提供一份详细的参考资料。我们将全面介绍Oracle11gDataGuard:Ho

这篇文章主要围绕Oracle 11g Data Guard: How to start and stop Redo Apply展开，旨在为您提供一份详细的参考资料。我们将全面介绍Oracle 11g Data Guard: How to start and stop Redo Apply，同时也会为您带来11G、12C Data Guard Physical Standby Switchover 转换参考手册、call、apply、bind 的区别？call 和 apply 哪个性能会更好？如何实现 call、apply、bind？、Centos 7 使用（Service iptables stop/start）关闭/打开防火墙 Failed to stop iptables.service: Unit iptables.s...、Data Guard Physical Standby Setup in Oracle Database 11g Release 2的实用方法。

本文目录一览：

Oracle 11g Data Guard: How to start and stop Redo Apply
11G、12C Data Guard Physical Standby Switchover 转换参考手册
call、apply、bind 的区别？call 和 apply 哪个性能会更好？如何实现 call、apply、bind？
Centos 7 使用（Service iptables stop/start）关闭/打开防火墙 Failed to stop iptables.service: Unit iptables.s...
Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Oracle 11g Data Guard: How to start and stop Redo Apply

1 Starting Redo Apply To start apply services on a physical standby database, ensure thephysical standby database is started and mounted and then start Redo Applyusing the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement. You

1 starting redo apply

To start apply services on a physical standby database, ensure thephysical standby database is started and mounted and then start Redo Applyusing the SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement.

You can specify that Redo Apply runs as a foreground session or as abackground process, and enable it with real-time apply.

To start Redo Apply in the foreground, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

alterdatabase recover managed standby database disconnect from session;

This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This does not disconnect the current SQL session.
To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

alterdatabase recover managed standby database using current logfile disconnect fromsession;

2 Stopping Redo Apply

To stop Redo Apply, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3 Advanced Configuration

3.1 Using Real-Time Apply to ApplyRedo Data Immediately

If the real-time apply feature is enabled, apply services can applyredo data as it is received, without waiting for the current standby redo logfile to be archived. This results in faster switchover and failover timesbecause the standby redo log files have been applied already to the standbydatabase by the time the failover or switchover begins.

Use the ALTER DATABASE statement to enable the real-time applyfeature, as follows:

For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.
For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

Real-time apply requires a standbydatabase that is configured with a standby redo log and that is in ARCHIVELOGmode.

The figure belowshowsa Data Guard configuration with a local destination and a standby destination.As the remote file server (RFS) process writes the redo data to standby redolog files on the standby database, apply services can recover redo from standbyredo log files as they are being filled.

3.2 Specifying a Time Delay for theApplication of Archived Redo Log Files

In some cases, you may want to create a time lag between the timewhen redo data is received from the primary site and when it is applied to thestandby database. You can specify a time interval (in minutes) to protectagainst the application of corrupted or erroneous data to the standby database.When you set a DELAY interval, it does not delay the transport of the redo datato the standby database. Instead, the time lag you specify begins when the redodata is completely archived at the standby destination.

Note:

If you define a delay for a destination that has real-time applyenabled, the delay is ignored.

Specifying a Time Delay

You can set a time delay on primary and standby databases using theDELAY=minutes attribute of theLOG_ARCHIVE_DEST_n initializationparameter to delay applying archived redo log files to the standby database. Bydefault, there is no time delay. If you specify the DELAY attribute withoutspecifying a value, then the default delay interval is 30 minutes.

Canceling a Time Delay

You can cancel a specified delay interval as follows:

For physical standby databases, use the NODELAY keyword of the RECOVER MANAGED STANDBY DATABASE clause:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
For logical standby databases, specify the following SQL statement:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;

These commands result in apply services immediately beginning toapply archived redo log files to the standby database, before the time intervalexpires.

Using Flashback Database as anAlternative to Setting a Time Delay

As an alternative to setting an apply delay, you can use FlashbackDatabase to recover from the application of corrupted or erroneous data to thestandby database. Flashback Database can quickly and easily flash back astandby database to an arbitrary point in time.

作者：xiangsir

QQ：444367417

MSN：xiangsir@hotmail.com

11G、12C Data Guard Physical Standby Switchover 转换参考手册

Switchover 转换

Step 1: switchover 切换先前检查

（1）确保主备两端 log_archive_config 和 db_unique_name 参数都已经正确设置。

需要注意的是，主备两端 db_unique_name 不能一样。

（2）在备库检查 MRP 进程是否正在运行

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE ''MRP%'';

（3）在主库检查是否是 REAL TIME APPLY 模式

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE

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

MANAGED REAL TIME APPLY

如果不是，需要执行如下：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

（4）在备库检查 LOG_ARCHIVE_MAX_PROCESSES 参数是否为 4 或者更高

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

（5）检查备库 online redo log 文件已经被清除

SQL> SELECT DISTINCT L.GROUP# FROM V$LOG L, V$LOGFILE LF

WHERE L.GROUP# = LF.GROUP#

AND L.STATUS NOT IN (''UNUSED'', ''CLEARING'',''CLEARING_CURRENT'');

SQL>ALTER DATABASE CLEAR LOGFILE GROUP <ORL GROUP# from the query above>;

如果报如下错误：

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

ALTER DATABASE CLEAR LOGFILE GROUP 2

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

需要执行 recover managed standby database cancel;

更改之后再执行 recover managed standby database using current logfile disconnect;

确定备库 db_file_name_convert、log_file_name_convert 两个参数已经设置，若未设置，可能会报如下错误：

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

ALTER DATABASE CLEAR LOGFILE GROUP 3

ERROR at line 1:

ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 3 thread 1: ''/data/crfdb11/redo03.log''

（6）确保没有大的 GAP 产生

在主库查询当前的归档序列

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

验证备库已应用到。

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = ''YES'' AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = ''CURRENT'') GROUP BY THREAD#;

（7）检查备库临时表空间是否存在

SQL> SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;

（8）检查备库数据文件表空间是否有 offline 的

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=''OFFLINE'';

SQL> ALTER DATABASE DATAFILE ‘datafile-name’ ONLINE;

Step 2: 主库 JOB 的处理

SQL> SELECT * FROM DBA_JOBS_RUNNING;

SQL> SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED

FROM DBA_SCHEDULER_JOBS

WHERE ENABLED=''TRUE''

AND OWNER <> ''SYS'';

SQL> SHOW PARAMETER job_queue_processes

SQL> ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID=’*’;

SQL> EXECUTE DBMS_SCHEDULER.DISABLE( <job_name> );

Step 3: 停止中间件

Step 4: 查看主库 switchover 状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO STANDBY

附： A:switchover_status 出现 session active/not allowed

当出现 session active 的时候表示还有活动的 session, 则运行

Alter database commit to switchover to physical standby with session shutdown;

当出现 not allowed 时，在官方文档说转换会不成功，但是我测试的时候成功了。

B.ora- 01153: an incompatible media recovery is active

运行下面代码

Alter database recover managed standby database disconnect from session;

Step 5: 将主库切换成备库

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

Step 6: 查看备库 switchover 状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

TO PRIMARY

附：若不是用此语句切换：

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown

补充：若出现：ORA-16139: media recovery required

是因为没有执行：

alter database recover managed standby database disconnect from session;

Step 7: 将备库切换成主库

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

Step 8: 打开新主库

SQL> ALTER DATABASE OPEN;

Step 9: 更正不匹配的临时文件

SQL> SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE

FROM V$TEMPFILE TMP, V$TABLESPACE TS

WHERE TMP.TS#=TS.TS#;

Step 10: 重启新备库

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

SQL> alter database open read only;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 11: 更改服务器 IP 地址

这个可以用 xmanager 连过去，在界面上改，也可以直接用命令来修改。

vi /etc/sysconfig/network-scripts/ifcfg-eth0

/etc/init.d/network restart

Step 12: JOB 的处理

SQL> ALTER SYSTEM SET job_queue_processes=<value> scope=both sid=’*’

SQL> EXECUTE DBMS_SCHEDULER.ENABLE(<for each job name>);

Step 13: 在主库查看 log_archive_dest 参数的状态是否有效

因为这里与监听和启动顺序有关，如果值为 error，则归档不能传送到备库。

SQL>select status,database_mode from v$archive_dest_status;

Status 状态为 valid 即可。如果有错误，一般都是启动顺序和监听问题。如果启动顺序没有问题，那么就把主库重新启动一下。

Step 14: 修改参数及 tnsnames.ora 文件

参考搭建文档在主库和备库修改以下参数

主库先修改此参数：

alter system set db_unique_name=''orcl'' scope=spfile;

重启后再修改下面参数：

alter system set log_archive_dest_1=''location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl'' sid=''*'';

alter system set log_archive_dest_2=''service=standby lgwr async COMPRESSION=ENABLE valid_for=(online_logfiles,primary_role) db_unique_name=standby'' scope=both;

alter system set fal_server=orcl scope=both sid=''*'';

alter system set log_archive_config=''dg_config=(orcl,standby)'' scope=both sid=''*'';

备库先修改此参数：

alter system set db_unique_name=''standby'' scope=spfile;

重启后再修改下面参数：

alter system set log_archive_dest_1=''location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby'' sid=''*'';

alter system set log_archive_dest_2='''' scope=both;

alter system set fal_server=orcl scope=both sid=''*'';

alter system set log_archive_config=''dg_config=(orcl,standby)'' scope=both sid=''*'';

修改参数完之后，修改 tnsnames.ora 文件，把 orcl 和 STANDBY 的 HOST 对换。

Step 15: 验证同步状态

在主库运行：

SQL> alter system switch logfile;

然后在主备库分别运行：

SQL>select sequence#,applied from v$archived_log;

确保 applied 都为 YES。

如果用：

SQL> select max(sequence#) from v$archived_log;

查看，如果主备库显示的一致，则只能表明归档已经传送到了备库，并不能表明该归档已经应用。而 applied 状态为 YES，就表明该归档已经应用到了备库。

如果等了很长时间 applied 都无法应用，可以尝试一下方法：

SQL>alter database recover managed standby database cancel;

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

这 2 个语句是取消 recover manager，在启动它。

Switchover 切换失败处理步骤

Step 1: 先关闭主库

SQL>shutdown immediate;

Step 2: 关闭备库

SQL>recover managed standby database cancel;

SQL>shutdown immediate;

Step 3: 在主库上的操作

SQL>startup mount;

SQL> alter database commit to switchover to primary;

SQL>alter database open;

SQL>alter system set log_archive_dest_2=''service=standby_db'' scope=both;

Step 4: 在备库上的操作

SQL>startup mount;

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

Step 5: 主库切换日志，在备库上的 alert 日志上查看是否成功。

call、apply、bind 的区别？call 和 apply 哪个性能会更好？如何实现 call、apply、bind？

相同点

bind、apply、call 都是用来绑定函数执行时this的指向（改变函数执行时的上下文），同时还可以传参，调用它们的对象必须是一个函数 Function。

区别

区别主要提现在传参上。

// call
Function.call(obj, arg1, arg2, arg3, ...);

// apply，有两个参数，第二个是类数组
Function.apply(obj, [argArray]);

// bind，bind 方法的返回值是函数，需要手动调用才会执行，而 apply 和 call 则是立即调用
Function.bind(obj, arg1, arg2, arg3, ...);

const obj = {
  name: ''小鸭子'',
};

function say (arg1, arg2) {
  console.log(`${this.name ? `我是一只${this.name}，` : ''''}${arg1}，${arg2}`);
}

say(''咿呀咿呀哟'', ''呱呱！''); // 咿呀咿呀哟，呱呱！

say.call(obj, ''咿呀咿呀哟'', ''呱呱！'') // 我是一只小鸭子，咿呀咿呀哟，呱呱！

say.apply(obj, [''咿呀咿呀哟'', ''呱呱！'']); // 我是一只小鸭子，咿呀咿呀哟，呱呱！

const manualSay = say.bind(obj, ''咿呀咿呀哟'', ''呱呱！''); // 绑定但不是立即执行
manualSay(); // 手动执行，输出：我是一只小鸭子，咿呀咿呀哟，呱呱！

性能比较

call 的性能要比 apply 好一些（尤其是传递给函数的参数超过三个的时候）

大概因为，apply 的第二个参数是数组，函数在执行时，解析数组获取参数，需要耗费性能。

call 实现

// call 实现
Function.prototype.myCall = function(context) {
  context = context || window; // 默认 window
  const args = [...arguments].slice(1); // 参数
  const fn = Symbol(); // 给 context 设置一个独一无二的属性，避免覆盖原有属性
  context[fn] = this; // 这里的 this 指向调用它的函数 fn
  const result = context[fn](...args); // 调用之
  delete context[fn]; // 删除添加的属性
  return result; // 返回值
}

say.myCall(obj, ''咿呀咿呀哟'', ''呱呱！'') // 我是一只小鸭子，咿呀咿呀哟，呱呱！

apply 实现

// apply 实现
Function.prototype.myApply = function(context, args) {
  context = context || window; // 默认 window
  args = [...args]; // 参数
  const fn = Symbol(); // 给 context 设置一个独一无二的属性，避免覆盖原有属性
  context[fn] = this; // 这里的 this 指向调用它的函数fn
  const result = context[fn](...args); // 调用之
  delete context[fn]; // 删除添加的属性
  return result; // 返回值
}

say.myApply(obj, [''咿呀咿呀哟'', ''呱呱！'']) // 我是一只小鸭子，咿呀咿呀哟，呱呱！

bind 实现

// bind 实现
Function.prototype.myBind = function(context) {
  context = context || window; // 默认 window
  const args = [...arguments].slice(1); // 参数
  const fn = this; // 这里的 this 指向调用它的函数 fn
  return function () {
    return fn.apply(context, args);
  };
}

const manualSay = say.myBind(obj, ''咿呀咿呀哟'', ''呱呱！'');
manualSay(); // 手动执行，输出：我是一只小鸭子，咿呀咿呀哟，呱呱！

总结起来，call、apply、bind 的实现，要处理好以下几点：

绑定上下文 context
处理好参数 arguments
使用 Symbol 存储临时函数

后记

call、apply、bind 是 JavaScript 中非常重要的概念，也是前端面试中的高频面试题，无论是工作还是面试中，我们总是绕不开它，因此必须要掌握它，本文参考了许多网上大神的资料，文中若有不对之处，欢迎指正。本面试题收录在 leetoffer。

Centos 7 使用（Service iptables stop/start）关闭/打开防火墙 Failed to stop iptables.service: Unit iptables.s...

https://www.cnblogs.com/skyheaving/archive/2020/02/08/12283656.html

　　背景：　

　　　　测试部署NetCore 项目到linux 系统时，窗口显示项目部署成功；但是本机无法访问（linux 在虚拟机上[ centos 7.6] ）; 如下图↓

　　能够相互ping 通，（Xshell 连接正常。），在centos 上也能正常访问，后来记起在进行linux 安装成功后，没有关闭防火墙，初步猜测是由于没有关闭防火墙导致，那关闭防火墙不就可以了？

　二、操作

　　　1、打开linux 终端，输入: service iptables stop ，就提示标题展示的异常；

　　2、然后输入：systemctl stop firewalld （暂时关闭防火墙）

　　　　查看防火墙状态：systemctl status firewalld

　　成功了！！！！

　　三、回顾总结

　　　　经过查找发现：在CentOS 7或RHEL 7或Fedora中防火墙由firewalld来管理；　如果想要使用 service iptables start/stop 命令，需要下载 iptables-services

　　　　1、在 Centos 7 下下载 iptables-services 时，需要执行以下命令

　　　　　　systemctl stop firewalld 　　　　--关闭防火墙　　

　　　　　　systemctl mask firewalld　　　

　　　　　yum install iptables-services 　　--安装iptables-services

　　　　　systemctl enable iptables　　　　--设置开机启动

　　　　　　service iptables save　　　　　　　--保存

　　　　2、操作linux 防火墙命令：　　　　　　

1:查看防火状态
    systemctl status firewalld
    service  iptables status

2:暂时关闭防火墙
    systemctl stop firewalld
    service  iptables stop

3:永久关闭防火墙
    systemctl disable firewalld
    chkconfig iptables off

4:重启防火墙
    systemctl enable firewalld
    service iptables restart  

5:永久关闭后重启（未测试）
    chkconfig iptables on

查找资料：1、https://www.cnblogs.com/jxldjsn/p/10794171.html

　　　　　2、https://blog.csdn.net/c233728461/article/details/52679558/

　如有不合理之处，请大家多多指教。

　　　　如果您觉得本文对您有帮助，欢迎点击“收藏”按钮！（/:微笑）欢迎转载,转载请注明出处。

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Home » Articles » 11g » Here

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Data Guard is the name for Oracle''s standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 9i physical standby setup method posted here.

You should probably be using the Data Guard Broker to configure and manage your standby database, as described here.

Assumptions
Primary Server Setup
- Logging
- Initialization Parameters
- Service Setup
- Backup Primary Database
- Create Standby Controlfile and PFILE
Standby Server Setup (Manual)
- Copy Files
- Start Listener
- Restore Backup
- Create Redo Logs
Standby Server Setup (DUPLICATE)
- Copy Files
- Start Listener
- Create Standby Redo Logs on Primary Server
- Create Standby using DUPLICATE
Start Apply Process
Test Log Transport
Protection Mode
Database Switchover
Failover
Flashback Database
Read-Only Standby and Active Data Guard
Snapshot Standby

Assumptions

You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I''ve used Oracle Linux 5.6 and Oracle Database 11.2.0.2.
The primary server has a running instance.
The standby server has a software only installation.

Primary Server Setup

Logging

Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL>

If it is noarchivelog mode, switch is to archivelog mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "DB11G" on the primary database.

SQL> show parameter db_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 DB11G

SQL> show parameter db_unique_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 DB11G

SQL>

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "DB11G_STBY".

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=''DG_CONFIG=(DB11G,DB11G_STBY)'';

Set suitable remote archive log destinations. In this case I''m using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=''SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT=''%t_%s_%r.arc'' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.

ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT=''DB11G_STBY'',''DB11G'' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=''DB11G_STBY'',''DB11G''  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

Service Setup

Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.

DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

Backup Primary Database

If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.

$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the primary database.

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

Create a parameter file for the standby database.

CREATE PFILE=''/tmp/initDB11G_stby.ora'' FROM SPFILE;

Amend the PFILE making the entries relevant for the standby database. I''m making a replica of the original server, so in my case I only had to amend the following parameters.

*.db_unique_name=''DB11G_STBY''
*.fal_server=''DB11G''
*.log_archive_dest_2=''SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G''

Standby Server Setup (Manual)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Archivelogs and backups
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Notice, the backups were copied across to the standby server as part of the FRA copy. If your backups are not held within the FRA, you must make sure you copy them to the standby server and make them available from the same path as used on the primary server.

Start Listener

Make sure the listener is started on the standby server.

$ lsnrctl start

Restore Backup

Create the SPFILE form the amended PFILE.

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE=''/tmp/initDB11G_stby.ora'';

Restore the backup files.

$ export ORACLE_SID=DB11G
$ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Create Redo Logs

Create online redo logs for the standby. It''s a good idea to match the configuration of the primary server.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE (''/u01/app/oracle/oradata/DB11G/online_redo01.log'') SIZE 50M;
ALTER DATABASE ADD LOGFILE (''/u01/app/oracle/oradata/DB11G/online_redo02.log'') SIZE 50M;
ALTER DATABASE ADD LOGFILE (''/u01/app/oracle/oradata/DB11G/online_redo03.log'') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.

ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo01.log'') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo02.log'') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo03.log'') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo04.log'') SIZE 50M;

Once this is complete, we can start the apply process.

Standby Server Setup (DUPLICATE)

Copy Files

Create the necessary directories on the standby server.

$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump

Copy the files from the primary to the standby server.

$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Start Listener

When using active duplicate, the standby server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Make sure the listener is started on the standby server.

$ lsnrctl start

Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.

ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo01.log'') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo02.log'') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo03.log'') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (''/u01/app/oracle/oradata/DB11G/standby_redo04.log'') SIZE 50M;

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary "init.ora" file.

$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE=''/tmp/initDB11G_stby.ora'';

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.

$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name=''DB11G_STBY'' COMMENT ''Is standby''
    SET LOG_ARCHIVE_DEST_2=''SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G''
    SET FAL_SERVER=''DB11G'' COMMENT ''Is primary''
  NOFILENAMECHECK;

A brief explanation of the individual clauses is shown below.

FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
NOFILENAMECHECK: Destination file locations are not checked.

Once the command is complete, we can start the apply process.

Start Apply Process

Start the apply process on standby server.

# Foreground redo apply. Session never returns until cancel. 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you need to cancel the apply process, issue the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

Provided you have configured standby redo logs, you can start real-time apply using the following command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.

ALTER SESSION SET nls_date_format=''DD-MON-YYYY HH24:MI:SS'';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

Check the new archived redo log has arrived at the standby server and been applied.

ALTER SESSION SET nls_date_format=''DD-MON-YYYY HH24:MI:SS'';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Protection Mode

There are three protection modes for the primary database:

Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

By default, for a newly created standby database, the primary database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL>

The mode can be switched using the following commands. Note the alterations in the redo transport attributes.

-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=''SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=''SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=''SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY'';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.

Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is shown here.

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

To switch the standby database into read-only mode, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Snapshot Standby

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.

If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

Make sure managed recovery is disabled.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.

SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL>

You can now do treat the standby like any read-write database.

To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

SQL>

The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.

For more information see:

Creating a Physical Standby Database
Converting a Failed Primary Into a Standby Database Using Flashback Database
Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2
Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 12c Release 1
Data Guard (9i)
Data Guard (11gR2) Setup using Oracle Grid Control
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE [ID 1075908.1]

关于Oracle 11g Data Guard: How to start and stop Redo Apply的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于11G、12C Data Guard Physical Standby Switchover 转换参考手册、call、apply、bind 的区别？call 和 apply 哪个性能会更好？如何实现 call、apply、bind？、Centos 7 使用（Service iptables stop/start）关闭/打开防火墙 Failed to stop iptables.service: Unit iptables.s...、Data Guard Physical Standby Setup in Oracle Database 11g Release 2等相关知识的信息别忘了在本站进行查找喔。

本文标签：