文档章节

Switch over and switch back – Oracle 11g Manual Data Guard Part-II

rootliu
 rootliu
发布于 2017/10/28 07:42
字数 1656
阅读 26
收藏 0

Switch over and switch back – Oracle 11g Manual Data Guard Part-II

Published about a year ago by Jignesh Jethwa

Oracle 11g Logo

In my Previous article we have covered How to configure oracle 11g Manual Data Guard, Now we will look into switch-over and switch-back activity.

Switch-over Pre-requisites:

One:

Check listener status on primary and standby database.

Primary Database Listener:

[oracle@PR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:49:31
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:44:06
Uptime 0 days 0 hr. 5 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Services Summary...
Service "RTS" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
Service "RTSXDB" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Standby Database Listener:

[oracle@DR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:51:55
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:45:18
Uptime 0 days 0 hr. 6 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DR.localdomain)(PORT=1521)))
Services Summary...
Service "RTSDR" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Two:

Check database mode on primary and standby database.

Primary Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Standby Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS MOUNTED

Three:

Check database role on primary and standby database.

Primary Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PRIMARY

Standby Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PHYSICAL STANDBY

Four:

No datafiles should be in recovery mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('RECOVER');
no rows selected

Five:

No datafiles should be in offline mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('OFFLINE');
no rows selected

Six:

No datafiles should be in backup mode on Primary and Standby database.

SQL> select file# from v$backup where status='ACTIVE';
no rows selected

Switch-over steps:

Step-A:

Shutdown primary database:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step-B:

Copy control file to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/prtemp/
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/prtemp/

Step-C:

Copy SPFile to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/prtemp/

Step-D:

Copy Redo log files to temporary location on primary database.

[oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS/
[oracle@PR RTS]$ ll *.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:08 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo03.log
[oracle@PR RTS]$ cp *.log /home/oracle/prtemp/

Step-E:

Copy needed archive log to DR site.

[oracle@PR RTS]$ cd /home/oracle/archdir/
[oracle@PR archdir]$ scp * oracle@DR:/home/oracle/archdir/

Step-F:

Get primary database to READ ONLY mode.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.
SQL> alter database open read only;
Database altered.

Step-G:

Apply all needed archive logs to standby database.

SQL> recover standby database until cancel;
ORA-00279: change 957836 generated at 04/26/2016 02:49:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_15_910140016.dbf
ORA-00280: change 957836 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 967269 generated at 04/26/2016 23:34:52 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_16_910140016.dbf
ORA-00280: change 967269 for thread 1 is in sequence #16
ORA-00278: log file '/home/oracle/archdir/1_15_910140016.dbf' no longer needed for this recovery
ORA-00279: change 967272 generated at 04/26/2016 23:34:53 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_17_910140016.dbf
ORA-00280: change 967272 for thread 1 is in sequence #17
ORA-00278: log file '/home/oracle/archdir/1_16_910140016.dbf' no longer needed for this recovery
.
..
...
ORA-00279: change 969653 generated at 05/09/2016 23:48:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_29_910140016.dbf
ORA-00280: change 969653 for thread 1 is in sequence #29
ORA-00278: log file '/home/oracle/archdir/1_28_910140016.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/archdir/1_29_910140016.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Step-H:

Shutdown standby database.

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Step-I:

Copy control file to temporary location on standby database.

[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/drtemp/
[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/drtemp/

Step-J:

Copy SPFile to temporary location on standby database.

[oracle@DR dbs]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/drtemp/

Step-K:

On Standby database, copy all files(from /home/oracle/drtemp/ directory) to primary database server(to /home/oracle/from_dr directory).

[oracle@DR ~]$ cd /home/oracle/drtemp/
[oracle@DR drtemp]$ ll
total 19044
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control02.ctl
-rw-r-----. 1 oracle oinstall 2560 May 11 23:27 spfileRTS.ora
[oracle@DR drtemp]$ scp * oracle@PR:/home/oracle/from_dr

Step-L:

On Primary database, copy all files(from /home/oracle/prtemp/ directory) to standby database server(to /home/oracle/from_pr directory).

[oracle@PR ~]$ cd /home/oracle/prtemp/
[oracle@PR prtemp]$ ll
total 172656
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control02.ctl
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo03.log
-rw-r-----. 1 oracle oinstall 2560 May 11 23:10 spfileRTS.ora
[oracle@PR prtemp]$ scp * oracle@DR:/home/oracle/from_pr

Step-M:

On Standby database, replace copied files(source directory: /home/oracle/prtemp/) to its appropriate locations:

[oracle@DR from_pr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@DR from_pr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@DR from_pr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora
[oracle@DR from_pr]$ cp *.log /u01/app/oracle/oradata/RTS/

Step-N:

Perform role switching on old standby database: (old standby database to new Primary database)

one:

Startup mount old standby database.

[oracle@DR ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.

Two:

Recover database.

SQL> recover database;
Media recovery complete.

Three:

Drop tempfiles(From temporary tablespace) from old standby database. (Note: If this is your first switch over then you may skip this step)

Four:

Open database:

SQL> alter database open;
Database altered.

Five:

Create new tempfile:

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/RTS/temp02.dbf' size 100m autoextend on maxsize unlimited;
Tablespace altered.

On Primary database, replace copied files(source directory: /home/oracle/drtemp/) to its appropriate locations:

[oracle@PR from_dr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR from_dr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@PR from_dr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora

Shutdown old primary database.

SQL> shut immediate;

Step-O:

Perform role switching on old primary database: (old primary database to new standby database)

One:

Start database with nomount state:

SQL> startup nomount;

Two:

Alter database to mount state with standby database:

SQL> alter database mount standby database;
Database altered.

Cheers!! Switch over activity successfully carried out, now lets perform post checks on both the database servers:

Post checks:

Check database mode and role on new primary database, it shall be READ WRITE and Primary.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Check database mode and role on new standby database, it shall be MOUNTED and PHYSICAL STANDBY.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

We are done with switch over activity.

Switch-back activity steps:

There is no difference in switch-over and switch-back steps. Use above steps to do it.
Cheers!!

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on How to open Standby Database in Read ONLY mode.

Thank you. 🙂

Share this:

本文转载自:https://dbatricksworld.com/switch-over-and-switch-back-oracle-11g-manual-data-guard-part-ii/

rootliu
粉丝 6
博文 404
码字总数 2796
作品 0
海淀
数据库管理员
私信 提问
Oracle 11g Data Guard Switchover and Switchback – Active Data Guard Part-II

Oracle 11g Data Guard Switchover and Switchback – Active Data Guard Part-II Published 3 years ago by Jignesh Jethwa In my Previous article we have covered How to configured ora......

rootliu
2017/10/28
96
0
DataGuardPhysicalStandbySetupinOracleDatabase11gR2

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 disast......

rootliu
2018/09/05
36
0
Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I

Steps to configure Oracle 11g Data Guard Physical Standby – Active Data Guard Part-I Published 3 years ago by Jignesh Jethwa Here in this article I am going to cover steps by s......

rootliu
2017/10/28
3.7K
0
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 disast......

rootliu
2017/10/27
29
0
Oracle11gR2DataGuardSetupusingOracleGridControl

Oracle Database 11g Release 2 Data Guard Setup using Oracle Grid Control This article describes the creation of a standby database using Oracle Database 11g Release 2 and the Or......

rootliu
2018/09/05
13
0

没有更多内容

加载失败,请刷新页面

加载更多

插入排序算法

《算法总纲目录》 1、定义     元素被分为有序区和无序区两部分。最初有序区只有一个元素。每次从无序区中选择一个元素,插入到有序区的位置,直到无序区变空。 2、代码 public class...

木九天
20分钟前
2
0
ApacheCN 翻译/校对/笔记整理活动进度公告 2019.10.18

注意 请贡献者查看参与方式,然后直接在 ISSUE 中认领。 翻译/校对三个文档就可以申请当负责人,我们会把你拉进合伙人群。翻译/校对五个文档的贡献者,可以申请实习证明。 请私聊片刻(52981...

ApacheCN_飞龙
22分钟前
2
0
Hands-on! 如何给 TiDB 添加新系统表

作者:黄东旭 “TiDB,你已经是一个成熟的数据库了,该学会用自己的 SQL 查自己的状态了。” 对于一个成熟的数据库来说,通过 SQL 来查询系统本身的状态再正常不过,对于 MySQL 来说 INFOMA...

TiDB
28分钟前
3
0
SpringBoot admin+Eureka+钉钉通知

SpringBoot admin+Eureka+钉钉通知 一、效果 登录账号+密码 监控服务 查看实时日志 钉钉通知 二、什么是Spring Boot Admin ? Spring Boot Admin是一个开源社区项目,用于管理和监控SpringB...

小白的成长
31分钟前
10
0
docker-rabbitmq

docker pull rabbitmqmkdir -p /rabbitmqdocker run -d \--name rabbitmq \--hostname rabbitmq \-v /rabbitmq:/var/lib/rabbitmq \-e RABBITMQ_DEFAULT_USER=root \-e RA......

李琼涛
33分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部