升级oracle 11.2.0.1至11.2.0.4 并配置DATAGuard
环境介绍:
master:
Redhat 6.8+Oracle 11.2.0.4 rac
db_name | db_unique_name | instance_name | service_names | 连接字符串 |
or11g | master_or11g | or11g1 | master_or11g | master_or11g_tns |
target:
Redhat 6.10+Oracle 11.2.0.1
master | db_name | db_unique_name | instance_name | service_names | 连接字符串 |
or11g | target_or11g | or11g | target_or11g | target_or11g_tns |
一、升级目标库
1.1、上传11.2.0.4的软件包并解压
1.2、升级前检查
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
SQL> set linesize 120
SQL> col comp_name format a40
SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
OWB 11.2.0.1.0 VALID
Oracle Application Express 3.2.1.00.10 VALID
Oracle Enterprise Manager 11.2.0.1.0 VALID
OLAP Catalog 11.2.0.1.0 VALID
Spatial 11.2.0.1.0 VALID
Oracle Multimedia 11.2.0.1.0 VALID
Oracle XML Database 11.2.0.1.0 VALID
Oracle Text 11.2.0.1.0 VALID
Oracle Expression Filter 11.2.0.1.0 VALID
Oracle Rules Manager 11.2.0.1.0 VALID
Oracle Workspace Manager 11.2.0.1.0 VALID
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 11.2.0.1.0 VALID
Oracle Database Packages and Types 11.2.0.1.0 VALID
JServer JAVA Virtual Machine 11.2.0.1.0 VALID
Oracle XDK 11.2.0.1.0 VALID
Oracle Database Java Packages 11.2.0.1.0 VALID
OLAP Analytic Workspace 11.2.0.1.0 VALID
Oracle OLAP API 11.2.0.1.0 VALID
18 rows selected.
SQL>
备份数据库(包括控制文件、日志文件、数据文件和参数文件)---这里是测试环境(略)
检查数据库是否有无效对象
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,status from dba_objects where status='INVALID';
no rows selected
SQL>
1.3、关闭数据库和监听(略)
1.4、执行软件和数据库升级
升级数据库:
出现警告,如不重要可以忽略
升级选下 选择“升级结束后重新编译无效对象”和“升级时区版本和timestamp with timezone 数据”以及“备份数据库”以免出现意外
指定是否在升级过程中移动数据库文件 选择“升级过程中不移动数据库文件”
恢复和诊断位置 默认:E:\app\Administrator\flash_recovery_area
概要 点击完成 开始升级 升级结束 查看升级结果报告,可以通过“配置数据库口令”查看是否有升级用户被锁;等待进度条变成100%,然后基本的升级就好了,就完成了,后续记得修改.bash_profile里面的ORACLE_HOME目录为新的11.2.0.4目录即可。
1.5、检查升级情况
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
修改compatible参数至11.2.0.4.0
**************************************************************************************************************************************************************************************************************************************************************************************************
二、主库准备配置DATAGuard
2.1、主库修改为归档模式
alter system set cluster_database=false scope=spfile sid='*';
startup mount;
alter database archivelog;
alter system set log_archive_dest_2='location=+fra' sid='*';
alter system set cluster_database=true scope=spfile;
shutdown immediate;
2.2、主库修改为强制日志模式
SQL> select name , open_mode, log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
--------- -------------------- ------------ ---
OR11G READ WRITE ARCHIVELOG NO
OR11G READ WRITE ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select name , open_mode, log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
--------- -------------------- ------------ ---
OR11G READ WRITE ARCHIVELOG YES
OR11G READ WRITE ARCHIVELOG YES
SQL>
2.3、创建口令文件
将主库口令文件传到备库;或者是备库使用orapwd命令创建口令文件
将主库下的一个实例的口令文件复制到其他实例和目标库,不然可能会出现:
复制rac2的口令文件到备库,结果连不上rac1
复制rac1的口令文件到备库,结果连不上rac2
[[email protected] dbs]$ scp orapwor11g1 or11g2:/u01/app/oracle/product/11.2.0/db_1/dbs/
[[email protected] dbs]$ scp orapwor11g1 or11g:/u01/app/oracle/product/11.2.0/db_4/dbs
2.4、主/备库配置监听(静态注册)和TNSNAME
SQL> alter system set db_unique_name='master_or11g' scope=spfile;
System altered.
SQL> alter system set db_unique_name='target_or11g' scope=spfile;
System altered.
配置静态注册---推荐用图形界面配置
将master的tnsname配置拷贝到target,同时将target的tnsname.ora拷贝到master
测试连接:
远程登录本地:
[[email protected] admin]$ sqlplus sys/[email protected]_or11g_tns as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 13 13:12:00 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
远程登录target:
[[email protected] admin]$ sqlplus sys/[email protected]_or11g_tns as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 13 13:12:11 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
远程登录本地:
[[email protected] admin]$ sqlplus sys/[email protected]_or11g_tns as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 13 13:12:34 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
远程登录master:
[[email protected] admin]$ sqlplus sys/[email protected]_or11g_tns as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 13 13:12:42 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
2.5、主库修改参数 ------以rac环境为例
alter system set db_unique_name='master_or11g' scope=spfile sid='*'; ------主库的db_unique_name名
alter system set log_archive_config='dg_config=(master_or11g,target_or11g)' scope=spfile sid='*'; ----dg_config配置的是主备库的db_unique_name名
alter system set log_archive_dest_2='location=+fra valid_for=(all_logfiles,all_roles) db_unique_name=master_or11g' scope=spfile sid='*'; ----主库第二路的本地归档
alter system set log_archive_dest_3='service=target_or11g_tns valid_for=(online_logfiles,primary_role) db_unique_name=target_or11g' scope=spfile sid='*'; ---主库的日志传到远端连接字符串是“rac_s”,表示备库的TNSNAME名
alter system set log_archive_dest_state_2=enable scope=spfile sid='*'; ---启用第二路归档
alter system set log_archive_dest_state_3=enable scope=spfile sid='*'; ----启用第三路归档
alter system set standby_file_management='auto' scope=spfile sid='*'; ---设置备库文件自动管理,可以在主库新建数据文件的时候自动新建备库
alter system set fal_server='target_or11g_tns' scope=spfile sid='*'; -----主库指向备库的TNSNAME
alter system set log_archive_format='%t_%s_%r.arch' scope=spfile sid='*';
2.6、创建standby redo比redo多一组
查询主库的日志组:
select a.GROUP#,a.STATUS,a.TYPE,a.MEMBER,a.IS_RECOVERY_DEST_FILE,b.BYTES/1024/1024||'M' from v$logfile a,v$log b where a.GROUP#=b.GROUP#;
创建standby redo需要比redo多一组
alter database add standby logfile '+FRA/or11g/onlinelog/standby_redo01.log' size 50M;
alter database add standby logfile '+FRA/or11g/onlinelog/standby_redo02.log' size 50M;
alter database add standby logfile '+FRA/or11g/onlinelog/standby_redo03.log' size 50M;
alter database add standby logfile '+FRA/or11g/onlinelog/standby_redo04.log' size 50M;
alter database add standby logfile '+FRA/or11g/onlinelog/standby_redo05.log' size 50M;
2.7、主库生成pfile,修改后传到目标库应用
SQL> create pfile='/home/oracle/initor11g.ora' from spfile;
File created.
SQL>
[[email protected] oracle]# cat /home/oracle/init.ora
*.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/or11g/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/or11g/control01.ctl','/u01/app/oracle/flash_recovery_area/or11g/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle'
*.DB_FILE_NAME_CONVERT='+DATA/or11g/datafile','/u01/app/oracle/oradata/or11g/'
*.db_file_name_convert='+DATA/or11g/tempfile/temp.265.986827323','cd /u01/app/oracle/oradata/or11g/temp.dbf'
*.db_domain=''
*.db_name='or11g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4621074432
*.db_unique_name='target_or11g'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=or11gXDB)'
*.fal_server='master_or11g_tns'
*.log_archive_config='dg_config=(master_or11g,target_or11g)'
*.log_archive_dest_2='location=/u02 valid_for=(all_logfiles,all_roles) db_unique_name=target_or11g'
*.log_archive_dest_3='location=/u02/standbylog valid_for=(standby_logfile,standby_roles) db_unique_name=target_or11g'
*.log_archive_dest_4='service=master_or11g_tns lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=master_or11g'
*.memory_target=568882624
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[[email protected] oracle]#
2.8、将目标数据库利用传过来的pfile启动到nomount状态
SQL> startup nomount pfile='/home/oracle/initor11g.ora';
2.9、备库登录rman
[email protected] ~]$ rman target sys/[email protected]_or11g_tns auxiliary sys/[email protected]_or11g_tns
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 13 16:14:35 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: OR11G (DBID=751923081)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
[[email protected] ~]$
遇到报错:
解决方法是:
在目标库的tnsname.ora中添加如下内容:
TARGET_OR11G_TNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.127.70)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED) ----添加这一行
(SERVICE_NAME = target_or11g)(UR=A) ----这一行里添加:(UR=A)
)
)
修改好后记得同样修改源库的tnsname.ora
TARGET_OR11G_TNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.127.70)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED) ----添加这一行
(SERVICE_NAME = target_or11g)(UR=A) ----这一行里添加:(UR=A)
)
)
2.10、执行数据拷贝
RMAN> duplicate target database for standby from active database;
注意:如果源库和目标库的文件路径名如果一样,那么需要加上nofilenamecheck参数,如:
RMAN> duplicate target database for standby from active database;
如果源库和目标库的文件名路径不一样,则不需要加上nofilenamecheck参数,如:
duplicate target database for standby from active database;
2.11、查看备库状态,已经被mount了:
select name,open_mode from v$database;
等待rman复制完毕
2.12、将备库置于active dataguard模式下
SQL> select DB_UNIQUE_NAME,OPEN_MODE,FORCE_LOGGING,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
打开数据库(target)
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/TARGET_OR11G/datafile/o1_mf_system_0jtd1695_.dbf'
SQL>
报错了------各种排查原因,最后发现是在配置参数的时候,将target的归档路径忘记改了还是写的+fra
修改归档路径,重启target数据库
执行如下命令:
SQL> startup mount pfile='/home/oracle/1.ora';
ORACLE instance started.
Total System Global Area 567869440 bytes
Fixed Size 2255272 bytes
Variable Size 415237720 bytes
Database Buffers 142606336 bytes
Redo Buffers 7770112 bytes
Database mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
回到master库上,执行切换归档,多切几次
SQL> alter database recover managed standby database cancel;
Database altered.
再次打开target,成功了!
SQL> alter database open;
Database altered.
SQL>
打开target库后,再次查看target状态:
注意一:在执行redo应用的时候,可以使用:
SQL> alter database recover managed standby database using current logfile disconnect from session;
或者:
SQL> alter database recover managed standby database using current logfile;
这两个的区别在于返不返回窗口的意思。加上“disconnect from session”则会返回当前窗口,可以继续做其他操作;如果不加,则会一直停在这里。
注意二:如果不想实时应用redo,则使用下面的方式启动:
SQL> alter database recover managed standby database disconnect from session;
2.13、验证DG同步情况
2.13.1、查询v$archived_log视图验证主库已经完成归档的redo log files:
master执行:
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
3 13-SEP-18 13-SEP-18
4 13-SEP-18 13-SEP-18
5 13-SEP-18 13-SEP-18
6 13-SEP-18 13-SEP-18
7 13-SEP-18 13-SEP-18
8 13-SEP-18 13-SEP-18
8 13-SEP-18 13-SEP-18
9 13-SEP-18 13-SEP-18
9 13-SEP-18 13-SEP-18
10 13-SEP-18 13-SEP-18
10 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
11 13-SEP-18 13-SEP-18
11 13-SEP-18 13-SEP-18
12 13-SEP-18 13-SEP-18
12 13-SEP-18 13-SEP-18
13 13-SEP-18 13-SEP-18
13 13-SEP-18 13-SEP-18
14 13-SEP-18 13-SEP-18
14 13-SEP-18 13-SEP-18
15 13-SEP-18 13-SEP-18
15 13-SEP-18 13-SEP-18
16 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
16 13-SEP-18 13-SEP-18
16 13-SEP-18 13-SEP-18
17 13-SEP-18 13-SEP-18
17 13-SEP-18 13-SEP-18
17 13-SEP-18 13-SEP-18
18 13-SEP-18 13-SEP-18
18 13-SEP-18 13-SEP-18
19 13-SEP-18 13-SEP-18
19 13-SEP-18 13-SEP-18
20 13-SEP-18 13-SEP-18
20 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
21 13-SEP-18 13-SEP-18
21 13-SEP-18 13-SEP-18
21 13-SEP-18 13-SEP-18
22 13-SEP-18 13-SEP-18
22 13-SEP-18 13-SEP-18
22 13-SEP-18 13-SEP-18
23 13-SEP-18 13-SEP-18
23 13-SEP-18 13-SEP-18
23 13-SEP-18 13-SEP-18
24 13-SEP-18 13-SEP-18
24 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
24 13-SEP-18 13-SEP-18
25 13-SEP-18 13-SEP-18
25 13-SEP-18 13-SEP-18
25 13-SEP-18 13-SEP-18
26 13-SEP-18 13-SEP-18
26 13-SEP-18 13-SEP-18
26 13-SEP-18 13-SEP-18
27 13-SEP-18 13-SEP-18
27 13-SEP-18 13-SEP-18
27 13-SEP-18 13-SEP-18
28 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
28 13-SEP-18 13-SEP-18
28 13-SEP-18 14-SEP-18
29 13-SEP-18 13-SEP-18
29 13-SEP-18 13-SEP-18
29 14-SEP-18 14-SEP-18
30 13-SEP-18 13-SEP-18
30 13-SEP-18 13-SEP-18
30 14-SEP-18 14-SEP-18
31 13-SEP-18 13-SEP-18
31 13-SEP-18 13-SEP-18
31 14-SEP-18 14-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
32 13-SEP-18 13-SEP-18
32 13-SEP-18 13-SEP-18
32 14-SEP-18 14-SEP-18
33 13-SEP-18 13-SEP-18
33 13-SEP-18 13-SEP-18
33 14-SEP-18 14-SEP-18
34 13-SEP-18 13-SEP-18
34 13-SEP-18 13-SEP-18
35 13-SEP-18 13-SEP-18
35 13-SEP-18 13-SEP-18
36 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
36 13-SEP-18 13-SEP-18
37 13-SEP-18 13-SEP-18
37 13-SEP-18 13-SEP-18
38 13-SEP-18 13-SEP-18
38 13-SEP-18 13-SEP-18
39 13-SEP-18 13-SEP-18
39 13-SEP-18 13-SEP-18
40 13-SEP-18 13-SEP-18
40 13-SEP-18 13-SEP-18
41 13-SEP-18 13-SEP-18
41 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
42 13-SEP-18 13-SEP-18
42 13-SEP-18 13-SEP-18
43 13-SEP-18 13-SEP-18
43 13-SEP-18 13-SEP-18
44 13-SEP-18 13-SEP-18
44 13-SEP-18 13-SEP-18
45 13-SEP-18 13-SEP-18
45 13-SEP-18 13-SEP-18
46 13-SEP-18 13-SEP-18
46 13-SEP-18 13-SEP-18
47 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
47 13-SEP-18 13-SEP-18
48 13-SEP-18 14-SEP-18
48 13-SEP-18 14-SEP-18
49 14-SEP-18 14-SEP-18
49 14-SEP-18 14-SEP-18
50 14-SEP-18 14-SEP-18
50 14-SEP-18 14-SEP-18
51 14-SEP-18 14-SEP-18
51 14-SEP-18 14-SEP-18
52 14-SEP-18 14-SEP-18
52 14-SEP-18 14-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
53 14-SEP-18 14-SEP-18
53 14-SEP-18 14-SEP-18
112 rows selected.
SQL>
2.13.2、master执行日志切换
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
master再次执行查询语句:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
3 13-SEP-18 13-SEP-18
4 13-SEP-18 13-SEP-18
5 13-SEP-18 13-SEP-18
6 13-SEP-18 13-SEP-18
7 13-SEP-18 13-SEP-18
8 13-SEP-18 13-SEP-18
8 13-SEP-18 13-SEP-18
9 13-SEP-18 13-SEP-18
9 13-SEP-18 13-SEP-18
10 13-SEP-18 13-SEP-18
10 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
11 13-SEP-18 13-SEP-18
11 13-SEP-18 13-SEP-18
12 13-SEP-18 13-SEP-18
12 13-SEP-18 13-SEP-18
13 13-SEP-18 13-SEP-18
13 13-SEP-18 13-SEP-18
14 13-SEP-18 13-SEP-18
14 13-SEP-18 13-SEP-18
15 13-SEP-18 13-SEP-18
15 13-SEP-18 13-SEP-18
16 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
16 13-SEP-18 13-SEP-18
16 13-SEP-18 13-SEP-18
17 13-SEP-18 13-SEP-18
17 13-SEP-18 13-SEP-18
17 13-SEP-18 13-SEP-18
18 13-SEP-18 13-SEP-18
18 13-SEP-18 13-SEP-18
19 13-SEP-18 13-SEP-18
19 13-SEP-18 13-SEP-18
20 13-SEP-18 13-SEP-18
20 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
21 13-SEP-18 13-SEP-18
21 13-SEP-18 13-SEP-18
21 13-SEP-18 13-SEP-18
22 13-SEP-18 13-SEP-18
22 13-SEP-18 13-SEP-18
22 13-SEP-18 13-SEP-18
23 13-SEP-18 13-SEP-18
23 13-SEP-18 13-SEP-18
23 13-SEP-18 13-SEP-18
24 13-SEP-18 13-SEP-18
24 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
24 13-SEP-18 13-SEP-18
25 13-SEP-18 13-SEP-18
25 13-SEP-18 13-SEP-18
25 13-SEP-18 13-SEP-18
26 13-SEP-18 13-SEP-18
26 13-SEP-18 13-SEP-18
26 13-SEP-18 13-SEP-18
27 13-SEP-18 13-SEP-18
27 13-SEP-18 13-SEP-18
27 13-SEP-18 13-SEP-18
28 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
28 13-SEP-18 13-SEP-18
28 13-SEP-18 14-SEP-18
29 13-SEP-18 13-SEP-18
29 13-SEP-18 13-SEP-18
29 14-SEP-18 14-SEP-18
30 13-SEP-18 13-SEP-18
30 13-SEP-18 13-SEP-18
30 14-SEP-18 14-SEP-18
31 13-SEP-18 13-SEP-18
31 13-SEP-18 13-SEP-18
31 14-SEP-18 14-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
32 13-SEP-18 13-SEP-18
32 13-SEP-18 13-SEP-18
32 14-SEP-18 14-SEP-18
33 13-SEP-18 13-SEP-18
33 13-SEP-18 13-SEP-18
33 14-SEP-18 14-SEP-18
34 13-SEP-18 13-SEP-18
34 13-SEP-18 13-SEP-18
34 14-SEP-18 14-SEP-18
35 13-SEP-18 13-SEP-18
35 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
35 14-SEP-18 14-SEP-18
36 13-SEP-18 13-SEP-18
36 13-SEP-18 13-SEP-18
37 13-SEP-18 13-SEP-18
37 13-SEP-18 13-SEP-18
38 13-SEP-18 13-SEP-18
38 13-SEP-18 13-SEP-18
39 13-SEP-18 13-SEP-18
39 13-SEP-18 13-SEP-18
40 13-SEP-18 13-SEP-18
40 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
41 13-SEP-18 13-SEP-18
41 13-SEP-18 13-SEP-18
42 13-SEP-18 13-SEP-18
42 13-SEP-18 13-SEP-18
43 13-SEP-18 13-SEP-18
43 13-SEP-18 13-SEP-18
44 13-SEP-18 13-SEP-18
44 13-SEP-18 13-SEP-18
45 13-SEP-18 13-SEP-18
45 13-SEP-18 13-SEP-18
46 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
46 13-SEP-18 13-SEP-18
47 13-SEP-18 13-SEP-18
47 13-SEP-18 13-SEP-18
48 13-SEP-18 14-SEP-18
48 13-SEP-18 14-SEP-18
49 14-SEP-18 14-SEP-18
49 14-SEP-18 14-SEP-18
50 14-SEP-18 14-SEP-18
50 14-SEP-18 14-SEP-18
51 14-SEP-18 14-SEP-18
51 14-SEP-18 14-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
52 14-SEP-18 14-SEP-18
52 14-SEP-18 14-SEP-18
53 14-SEP-18 14-SEP-18
53 14-SEP-18 14-SEP-18
54 14-SEP-18 14-SEP-18
54 14-SEP-18 14-SEP-18
55 14-SEP-18 14-SEP-18
55 14-SEP-18 14-SEP-18
56 14-SEP-18 14-SEP-18
56 14-SEP-18 14-SEP-18
57 14-SEP-18 14-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
57 14-SEP-18 14-SEP-18
58 14-SEP-18 14-SEP-18
58 14-SEP-18 14-SEP-18
124 rows selected.
SQL>
2.13.3、target验证归档传送情况:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
16 13-SEP-18 13-SEP-18
17 13-SEP-18 13-SEP-18
20 13-SEP-18 13-SEP-18
21 13-SEP-18 13-SEP-18
22 13-SEP-18 13-SEP-18
23 13-SEP-18 13-SEP-18
24 13-SEP-18 13-SEP-18
25 13-SEP-18 13-SEP-18
26 13-SEP-18 13-SEP-18
27 13-SEP-18 13-SEP-18
28 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
29 13-SEP-18 13-SEP-18
30 13-SEP-18 13-SEP-18
31 13-SEP-18 13-SEP-18
32 13-SEP-18 13-SEP-18
33 13-SEP-18 13-SEP-18
34 13-SEP-18 13-SEP-18
35 13-SEP-18 13-SEP-18
36 13-SEP-18 13-SEP-18
37 13-SEP-18 13-SEP-18
38 13-SEP-18 13-SEP-18
39 13-SEP-18 13-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
40 13-SEP-18 13-SEP-18
41 13-SEP-18 13-SEP-18
42 13-SEP-18 13-SEP-18
43 13-SEP-18 13-SEP-18
44 13-SEP-18 13-SEP-18
45 13-SEP-18 13-SEP-18
46 13-SEP-18 13-SEP-18
47 13-SEP-18 13-SEP-18
48 13-SEP-18 14-SEP-18
49 14-SEP-18 14-SEP-18
50 14-SEP-18 14-SEP-18
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
51 14-SEP-18 14-SEP-18
52 14-SEP-18 14-SEP-18
53 14-SEP-18 14-SEP-18
54 14-SEP-18 14-SEP-18
55 14-SEP-18 14-SEP-18
56 14-SEP-18 14-SEP-18
57 14-SEP-18 14-SEP-18
58 14-SEP-18 14-SEP-18
41 rows selected.
SQL>
2.13.4、验证target归档应用情况:
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
16 YES
17 NO
20 YES
21 NO
22 NO
23 NO
24 NO
25 NO
26 NO
27 NO
28 NO
SEQUENCE# APPLIED
---------- ---------
29 NO
30 NO
31 NO
32 NO
33 NO
34 NO
35 NO
36 NO
37 NO
38 NO
39 NO
SEQUENCE# APPLIED
---------- ---------
40 NO
41 NO
42 NO
43 NO
44 NO
45 NO
46 NO
47 NO
48 NO
49 NO
50 NO
SEQUENCE# APPLIED
---------- ---------
51 NO
52 NO
53 NO
54 NO
55 NO
56 NO
57 NO
58 NO
41 rows selected.
SQL>
结果显示好多为NO,可以redo没有应用,让target应用redo:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
再来查看:
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
16 YES
17 YES
18 YES
19 YES
20 YES
20 YES
21 YES
21 YES
22 YES
22 YES
23 YES
SEQUENCE# APPLIED
---------- ---------
23 YES
24 YES
24 YES
25 YES
25 YES
26 YES
26 YES
27 YES
27 YES
28 YES
28 YES
SEQUENCE# APPLIED
---------- ---------
29 YES
29 YES
30 YES
30 YES
31 YES
31 YES
32 YES
32 YES
33 YES
33 YES
34 YES
SEQUENCE# APPLIED
---------- ---------
34 YES
35 IN-MEMORY
35 YES
36 YES
37 YES
38 YES
39 YES
40 YES
41 YES
42 YES
43 YES
SEQUENCE# APPLIED
---------- ---------
44 YES
45 YES
46 YES
47 YES
48 YES
49 YES
50 YES
51 YES
52 YES
53 YES
54 YES
SEQUENCE# APPLIED
---------- ---------
55 YES
56 YES
57 YES
58 YES
59 rows selected.
SQL>
现在已经应用了
2.13.5、停止redo apply
alter database recover managed standby database cancel;
2.14、验证DG的switchover
2.14.1、验证master能否切换成target
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL>
如果是 TO STANDBY 或者 SESSION ACTIVE,表示master可以切换成standby role。 其他值就不能切换,因为其他值表明DG 的环境可能已经破坏了。
2.14.2、将master切换成target
如果查询结果是:TO STANDBY,那么使用:alter database commit to switchover to physical standby;这条语句切换。
如果查询结果是:SESSIONS ACTIVE,那么使用:alter database commit to switchover to physical standby with session shutdown;
显示to standby 表示满足转换条件。
如果显示session active表示还有活动会话,关闭活动会话再检查
如果结果为session active,在切换的时候可以指定with session shutdown子句强制关闭活动会话
2.14.3、shutdown原master并启动到mount状态
shutdown immediate
satrtup mount
2.14.4、验证master切换后的状态
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY RECOVERY NEEDED
SQL>
2.14.5、验证原target能否切换成master
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL>
2.14.6、将原target切换为master
如果查询结果是:TO PRIMARY,那么使用:alter database commit to switchover to primary;这条语句切换;
如果查询结果是:SESSIONS ACTIVE,那么使用:alter database commit to switchover to primary with session shutdown;这条语句进行切换。
2.14.7、打开原target数据库
alter database open;
2.14.8、查询原target角色状态
SQL> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE PRIMARY TO STANDBY
SQL>
2.14.9、以read only的方式打开原master数据库
SQL> alter database open read only;
Database altered.
SQL>
2.14.10、原master执行同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
2.14.11、验证switchover结果
查询原target归档发送情况:SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
验证原master归档应用情况:SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
原target创建一个新表:scott.aa并查询数据:
SQL> create table scott.aa as select * from scott.emp;
Table created.
SQL> select * from scott.aa;
原master上查询:select * from scott.aa; ---成功查询到数据
SQL> SELECT DEST_ID, STATUS,APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';
DEST_ID STATUS APPLIED_SCN
---------- --------- -----------
4 VALID 1162661
SQL>
2.15、验证DG的Failover
2.15.1、处理归档日志的gap
在master查看target是否有gap:
sql> select thread#, low_sequence#, high_sequence# from v$archive_gap;
如果有,将对应的归档文件copy到备库,在注册它:
sql>alter database register physical logfile 'log_file_path';
注意:如果有Gap存在,并且没有解决。那么是不能正常的进行一个Failover。 只能进行一个强制的Failover。 这种情况下会有数据丢失。
2.15.2、解决gap问题后,进行切换
2.15.2.1、取消Apply Service
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish;-- [force|wait|nowait]
----这一步是关键
在执行这个命令的时候,如果主库和备库之间的网络中断了。 那么备库的RFS进程就会等待网络的连接,直到TCP超时。 因此在这种情况下,我们就需要加上Force 关键字。
--在oracle 10gR2之前的版本:没有备库日志文件:
SQL> alter database recover managed standby database finish skip standby logfile;
注意:如果执行了这条命令,就不能在进行recover standby database;
2.15.3、 将target切换为master
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup
2.16、验证DG的保护模式
2.14.1、查询master与target的保护模式
master查看当前的保护模式:
SQL> select protection_mode, protection_level, database_role role, switchover_status from v$database;
PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
SQL>
2.14.2、将master模式切换为最大可用性: