19c ADG部署
因客户需要部署19c DG环境，以前一直是11g和12c部署，故测试下19c部署
1、DG基础环境
ORACLE 主库IP：111.111.111.203 SID:DT db_name='DT' 主机名：test19c
ORACLE 备库IP：111.111.111.204 SID:dgtest db_name='DT' 主机名：testdg19c
主库归档目录物理路径：
SQL> archive log list
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
主库datafile物理路径
/u01/app/oradata/
/u01/app/oradata/DT
主库redo物理路径
/u01/app/oradata/DT
参数*log_archive_config='dg_config(pri,std)'，以确保主备库数据库能够互相识别对方。
查看归档是否有报错
select status,error from v$archive_dest;
2、修改主库配置文件initTESTDB.ora
这里现在数据库里修改相关的参数，与DG的参数就只与几个参数相关，大概就是日志，文件的位置的转换，GAP的处理，其实GAP已经会自动的处理，不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。在修改完之后重新创建了pfile文件
先创建spfile，修改完后重新生成pfile
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(DT,dgtest)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DT';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=dgtest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set FAL_SERVER=dgtest;
SQL> alter system set FAL_CLIENT=DT;
SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oradata/dgtest','/u01/app/oradata/DT' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oradata/dgtest','/u01/app/oradata/DT' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> create pfile from spfile;
DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数必须重启数据库生效
下面是修改好之后重新生成的pfile文件
DT.__data_transfer_cache_size=0
DT.__db_cache_size=247463936
DT.__inmemory_ext_roarea=0
DT.__inmemory_ext_rwarea=0
DT.__java_pool_size=4194304
DT.__large_pool_size=20971520
DT.__oracle_base='/u01/app'#ORACLE_BASE set from environment
DT.__pga_aggregate_target=293601280
DT.__sga_target=549453824
DT.__shared_io_pool_size=16777216
DT.__shared_pool_size=243269632
DT.__streams_pool_size=0
DT.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/admin/DT/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oradata/DT/control01.ctl','/u01/app/oradata/DT/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oradata/dgtest','/u01/app/oradata/DT'
*.db_name='DT'
*.db_unique_name='DT'
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DTXDB)'
*.fal_client='DT'
*.fal_server='dgtest'
*.local_listener='LISTENER_DT'
*.log_archive_config='DG_CONFIG=(DT,dgtest)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DT'
*.log_archive_dest_2='SERVICE=dgtest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgtest'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='/u01/app/oradata/dgtest','/u01/app/oradata/DT'
*.memory_max_target=903741824
*.memory_target=839524096
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=480
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
3、 修改备库的配置文件为：initdgtest.ora
DT.__data_transfer_cache_size=0
DT.__db_cache_size=339738624
DT.__inmemory_ext_roarea=0
DT.__inmemory_ext_rwarea=0
DT.__java_pool_size=4194304
DT.__large_pool_size=20971520
DT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DT.__pga_aggregate_target=201326592
DT.__sga_target=641728512
DT.__shared_io_pool_size=12582912
DT.__shared_pool_size=247463936
DT.__streams_pool_size=0
DT.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/admin/dgtest/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oradata/dgtest/control01.ctl','/u01/app/oradata/dgtest/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oradata/DT','/u01/app/oradata/dgtest'
*.db_name='DT'
*.db_unique_name='dgtest'
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgtestXDB)'
*.fal_client='dgtest'
*.fal_server='DT'
*.local_listener='LISTENER_dgtest'
*.log_archive_config='DG_CONFIG=(DT,dgtest)'
*.log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest'
*.log_archive_dest_2='SERVICE=DT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DT'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='/u01/app/oradata/DT','/u01/app/oradata/dgtest'
*.memory_max_target=903741824
*.memory_target=839524096
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=480
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
注意：
1> Linux端配置的pfile文件中，所有windows的路径都要用大写，因为在duplication过程中，windows端都是按照大写路径来传输的！
如果用小写或者大小写混合，则无法识别路径，会有问题！
2> 在duplication过程中，虽然是在主库操作，但是datafile和logfile的路径转换却认的是备库的pfile文件中的转换路径！
4、修改主库的listener.ora文件 ---如果不配置会报错rman-04006 ora-12514
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DT)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = DT)
)
)
ADR_BASE_LISTENER = D:\app\Administrator
5、修改主库的tnsnames.ora文件
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_DT =
(ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
DT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DT)
)
)
dgtest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdg19c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgtest)
)
)
6、修改备库的listener.ora文件
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdg19c)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
(PROGRAM = extproc)
#(ENVS = "EXTPROC_DLLS=ONLY:/u01/app/oracle/product/19.2.0/dbhome_1/oraclr11.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = dgtest)
(ORACLE_HOME = /u01/app/oracle/product/19.2.0/dbhome_1)
(SID_NAME = dgtest)
)
)
7、修改备库的tnsnames.ora文件
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_DT =
(ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
DT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test19c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DT)
)
)
dgtest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdg19c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgtest)
)
)
8、备库生成orapwd文件
orapwd file=orapwdgtest password=oracle
注：为防止密码问题导致无法访问，最好直接把主库的orapw文件拷贝过来然后更名orapwSID就可以了
9、确认主库和备库都启动监听：lsnrctl start
10、主库设置为归档模式
alter database archivelog
设置主数据库为日志强制写状态
alter database force logging;
查看状态日志强制写状态为YES
select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
11、查看主库数据库的日志组个数与大小，因为我们创建standby日志组的个数是原日志组个数+1再与thread的积，size不能小于原日志文件的大小。
SQL> select group#,THREAD#,bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 1 200
SQL> select member from v$logfile;
MEMBER
--------------------------------------
/u01/app/oradata/DT/redo03.log
/u01/app/oradata/DT/redo02.log
/u01/app/oradata/DT/redo01.log
12、创建standby日志组，个数是原日志组个数+1再与实例数的积，size不能小于原日志文件的大小
注：RAC环境注意实例有几个，新建standby日志组的路径可与原日志组相同。
SQL> alter database add standby logfile '/u01/app/oradata/DT/standby01.log' size 200m;
Database altered.
alter database add standby logfile '/u01/app/oradata/DT/standby01.log' size 200m;
alter database add standby logfile '/u01/app/oradata/DT/standby02.log' size 200m;
alter database add standby logfile '/u01/app/oradata/DT/standby03.log' size 200m;
alter database add standby logfile '/u01/app/oradata/DT/standby04.log' size 200m;
创建完成后查询是否成功
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------
3 ONLINE /u01/app/oradata/DT/redo03.log
2 ONLINE /u01/app/oradata/DT/redo02.log
1 ONLINE /u01/app/oradata/DT/redo01.log
4 STANDBY /u01/app/oradata/DT/standby01.log
5 STANDBY /u01/app/oradata/DT/standby02.log
6 STANDBY /u01/app/oradata/DT/standby03.log
7 STANDBY /u01/app/oradata/DT/standby04.log
7 rows selected.
13、启动备库到NOMOUNT
$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/backup/INITtestdb.ORA';
注：如果pfile文件放在默认路径，且文件名正确的话。不需要指定pfile路径，直接startup nomount即可。
14、duplicate开始
在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错)
rman target sys/oracle auxiliary sys/oracle@dgtest
rman> duplicate target database for standby nofilenamecheck from active database;
经资料查询，发现在duplicate传输时，windows下全部是大写字符，所以，还得修改
DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT参数的路径，全用大写
*************************错误信息*************************
19c测试时由于备库的环境变量ORACLE_HOME配置有误，导致始终无法连接到备库，报错如下：
[oracle@test19c admin]$ rman target sys/oracle auxiliary sys/oracle@dgtest
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jun 17 18:13:10 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DT (DBID=1254913786)
connected to auxiliary database (not started)
RMAN> exit
检查发现在.bash_profile文件中，ORACLE_BASE的路径最后加了一个/，导致在ORACLE_HOME中多了一个/
ORACLE_BASE=/u01/app/oracle/; export ORACLE_BASE ---/u01/app/oracle不该多那个/啊。。。
ORACLE_HOME=$ORACLE_BASE/product/19.2.0/dbhome_1; export ORACLE_HOME
最终使用echo $ORACLE_HOME时看到的路径如下：
/u01/app/oracle//product/19.2.0/dbhome_1
但是最无奈的是你输入cd $ORACLE_HOME的时候却可以进入正确的路径，然后pwd会显示：/u01/app/oracle/product/19.2.0/dbhome_1
所以之前检查时，始终认为环境变量没有问题。最终将ORACLE_BASE修改为/u01/app/oracle 然后重启监听，重启备库就好了
总结：还是使用echo来检查各种环境变量，不要直接cd进去检查。
------分割线------
注意db_recovery_file_dest_size的大小，上次部署12c生产环境就是大小有问题，修改后解决：
SQL> alter system set db_recovery_file_dest_size=60G;
***************************End***************************
15、open备库，并恢复到自动恢复状态
确认备库状态：
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
把备库启动到open only下面：
SQL> alter database open read only;
在备库上启动数据库到恢复管理模式，并开始准备从主库接受归档日志的传输：
SQL> alter database recover managed standby database using current logfile disconnect from session;
*************************错误信息*************************
备库启动报错：
SQL> alter database open read only;
alter database open read only
*
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/oradata/dgtest/system01.dbf'
在主备库上查看alert日志，发现备库alert日志有如下信息：
PR00 (PID:7481): Please verify that primary database is transporting redo logs to the standby database
根据信息检查，发现redo根本没过来，因为在备库归档目录没有看到一个归档文件！！！
目前定位就是无法传输归档，于是在主备库检查归档是否有报错：
select status,error from v$archive_dest;
在主库发现报错：
SQL> select status,error from v$archive_dest;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
ERROR ORA-16086: Redo data cannot be written to the standby redo log
这种报错一般也是和配置文件相关，网上也有相关文档和排错思路。
经过多次测试发现是和db_unique_name参数没有配置有关：
检查发现备库的db_unique_name在没有配置的情况下默认也叫DT，和主库一致。
这就导致了主备库因为db_unique_name一致而无法区分，进而导致redo无法传输，也就没有归档了。
SQL> show parameter name
NAME TYPE VALUE
---------------------------- ----------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string /u01/app/oradata/DT, /u01/app/oradata/dgtest
db_name string DT
db_unique_name string DT
global_names boolean FALSE
instance_name string dgtest
lock_name_space string
log_file_name_convert string /u01/app/oradata/DT, /u01/app/oradata/dgtest
pdb_file_name_convert string
processor_group_name string
service_names string DT
SQL>
上面的信息可以看到instance_name是dgtest，但是db_unique_name还是DT，所以导致redo无法传输。
最后在备库上修改pfile文件，加上db_unique_name的配置就解决问题了。
最好是主备库都在pfile中定义一下db_unique_name!
本次部署环境，19c主库安装好后，生成的pfile没有定义db_unique_name，所以也就忽略了这个问题。
-------分割线，下面步骤操作有问题，应该先open库----------
回到原主库启动STANDBY开始接收并恢复主库的日志
alter database recover managed standby database using current logfile disconnect from session;
在mount状态下输入上面语句，导致数据库无法open,所以需要退出这个状态
alter database recover managed standby database cancel;
***************************End***************************
16、主库检查LNS进程：
select process,status from v$managed_standby;
查看进程，看有没有LNS进程，如果没有则需要检查DG环境
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
DGRD ALLOCATED
ARCH CLOSING
DGRD ALLOCATED
ARCH OPENING
ARCH CONNECTED
ARCH CONNECTED
LNS OPENING
DGRD ALLOCATED
LNS WRITING
9 rows selected.
17、检查主备库角色和其他信息
在备库端查看其角色是否已经是physical standby,检查当前备库的模式PROTECTION_MODE
SQL> select DATABASE_ROLE,protection_mode,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE READ ONLY WITH APPLY
在主库查看其角色
SQL> select DATABASE_ROLE,open_mode from gv$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
在备库查看data guard为哪种日志接受方式
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
DGRD N/A 0 ALLOCATED
DGRD N/A 0 ALLOCATED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
RFS Archival 0 IDLE
RFS LGWR 31 IDLE
RFS UNKNOWN 0 IDLE
MRP0 N/A 31 APPLYING_LOG
10 rows selected.
18、检查主备库sequence#
select max(sequence#) from v$archived_log;
select sequence#,name,standby_dest,applied,deleted from v$archived_log where archived='YES' order by sequence# desc;
主库：
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
30
备库
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
30
19、测试日志队列是否能正常传输
在备库查看日志的队列情况
SQL> select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
30 YES 18-JUN-19 18-JUN-19
在主库进行强制归档
ALTER SYSTEM ARCHIVE LOG CURRENT;
alter system switch logfile;
在备库查看日志的队列情况,看新的归档日志有没有正常传输过来
SQL> select sequence#,applied,first_time,next_time from v$archived_log order by sequence#;
SEQUENCE# APPLIED FIRST_TIM NEXT_TIME
---------- --------- --------- ---------
30 YES 18-JUN-19 18-JUN-19
31 NO 18-JUN-19 18-JUN-19
32 IN-MEMORY 18-JUN-19 18-JUN-19
检查下两边的日志同步情况
select sequence# from v$archived_log where applied='YES';
看看有没有写的redo log:
select sequence#,applied from v$archived_log;
20、查看DG是否正常工作，这一步主要看归档有无报错
select dest_id,error,status from v$archive_dest where status='ERROR';
SQL> select dest_id,error,status from v$archive_dest where status='ERROR';
no rows selected
也可以直接查看所有归档目录信息：
select dest_id,error,status from v$archive_dest
21、在主库新建表空间、用户、表，并插入数据来测试备库是否能及时同步数据
主库创建测试表空间：
SQL> create tablespace test datafile '/u01/app/oradata/DT/dt01.dbf' size 50m;
主备库查看数据文件状态
select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile;
主库：
SQL> select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile;
FILE# CREATION_ STATUS NAME BYTES
------- --------- ------- ------------------------------------------ ----------
1 04-FEB-19 SYSTEM /u01/app/oradata/DT/system01.dbf 943718400
3 04-FEB-19 ONLINE /u01/app/oradata/DT/sysaux01.dbf 555745280
4 04-FEB-19 ONLINE /u01/app/oradata/DT/undotbs01.dbf 68157440
5 18-JUN-19 ONLINE /u01/app/oradata/DT/dt01.dbf 52428800
7 04-FEB-19 ONLINE /u01/app/oradata/DT/users01.dbf 5242880
备库：
SQL> select FILE#,CREATION_TIME,STATUS,NAME,BYTES from v$datafile;
FILE# CREATION_ STATUS NAME BYTES
------- --------- ------- ------------------------------------------ ----------
1 04-FEB-19 SYSTEM /u01/app/oradata/dgtest/system01.dbf 943718400
3 04-FEB-19 ONLINE /u01/app/oradata/dgtest/sysaux01.dbf 555745280
4 04-FEB-19 ONLINE /u01/app/oradata/dgtest/undotbs01.dbf 68157440
5 18-JUN-19 ONLINE /u01/app/oradata/dgtest/dt01.dbf 52428800
7 04-FEB-19 ONLINE /u01/app/oradata/dgtest/users01.dbf 5242880
主库操作：
1> 创建用户
create user dgtest default tablespace test identified by oracle;
grant dba to dgtest;
2> 切换用户
sqlplus dgtest/oracle
3> dgtest用户下创建表和插入测试数据
--创建表
create table dgtest (
id number(9) not null primary key,
classname varchar2(40) not null
);
--插入数据
insert into dgtest values(28,'class one');
insert into dgtest values(29,'detest one');
commit;
在备库执行查询：
select * from dgtest.dgtest;
SQL> select * from dgtest.dgtest;
ID CLASSNAME
------ ----------------------------------------
28 class one
29 detest one
删除测试数据：
drop tablespace test including contents and datafiles;
drop user dgtest cascade;
到此DG环境部署完毕，数据可以正常同步。
--------------------------------------------------------------
------------------------主备库切换测试------------------------
--------------------------------------------------------------
#切换的两种方式switchover和failover
switchover 切换：主库与备库数据同步正常情况下的切换，主要用于主备维护、切换演练等；
failover 切换：主库与备库在数据未同步情况下的强制切换，主要用于主库宕机、故障情况下切换；
一、switchover
Oracle 物理DG切换
在进行DATA GUARD的物理STANDBY切换前需要注意：
确认主库和备库间网络连接通畅；
确认没有活动的会话连接在数据库中；
确保STANDBY数据库处于ARCHIVELOG模式；
如果设置了REDO应用的延迟，那么将这个设置去掉；
确保配置了主库和备库的初始化参数，使得切换完成后，DATA GUARD机制可以顺利的运行。
#准备工作：
确认当前主库只有当前会话连接:
set pages 100 linesize 1000;
select SWITCHOVER_STATUS from v$database;
SQL> select SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
select count(*) from v$session where username is not null;
SQL> select count(*) from v$session where username is not null;
COUNT(*)
----------
2
若有多于一个会话则：
select sid,serial# from v$session where username is not null;
SQL> select sid,serial# from v$session where username is not null;
SID SERIAL#
----- ----------
2 10746
621 13864
select userenv('sid') from dual;
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
621
则执行下面语句杀掉：alter system kill session 'sid,serial#';
SQL> alter system kill session '621,13864';
alter system kill session '621,13864'
*
ERROR at line 1:
ORA-00027: cannot kill current session
原来621是当前会话，可以不用理会。
#正式切换：
主库与备库数据同步，且正常运行，将主库与备库的角色互换，也可以互换回来。
切换前，务必检查当前主库与备库的归档是否是同步的，确认同步后再执行切换
主库切备库
1、查看库的角色
主库：
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
CURRENT READ WRITE PRIMARY TO STANDBY
SWITCHOVER_STATUS为TO STANDBY，说明主库可以切换为备库
备库：
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
STANDBY READ ONLY PHYSICAL STANDBY NOT ALLOWED
SWITCHOVER_STATUS为NOT ALLOWED，这是备库的正常状态。
在主库首先切换后，我们再查询备库状态会发现SWITCHOVER_STATUS有改变
2、对主库进行切换
SQL> alter database commit to switchover to physical standby with session shutdown;
此时主库已经开始切换到备库的过程了，而备库的SWITCHOVER_STATUS已经变成TO PRIMARY
3、此时主库已经关闭，打到read only或同步状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 905967800 bytes
Fixed Size 8902840 bytes
Variable Size 788529152 bytes
Database Buffers 100663296 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
4、再查主库的角色，确认已经切换为备库
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
STANDBY READ ONLY PHYSICAL STANDBY TO PRIMARY
注：这一步操作完成后，此时主备库都是open read only状态，且SWITCHOVER_STATUS都为TO PRIMARY。
此时已经没有真正主库存在的了，主库已经切换为备库，备库还未切换成主库。
也就是说此时主备库都能切换为真正的主库
5、在备库上启动数据库到恢复管理模式，并开始准备从主库接受归档日志的传输。
SQL> alter database recover managed standby database using current logfile disconnect from session;
注：这一步不执行的话，OPEN_MODE状态始终是READ ONLY，无法变成正常的READ ONLY WITH APPLY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
查资料显示，12c之后使用上面这个语句来APPLY，黄色部分为和11gR2的区别
6、再查主库的角色，确认已经切换为备库，且OPEN_MODE为READ ONLY WITH APPLY
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------- -------------------- ---------------- --------------------
STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY
备库切主库
1、查看备库角色：
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------- -------------------- ---------------- --------------------
STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY
SWITCHOVER_STATUS为TO PRIMARY，说明备库可以切换为主库
2、备切主命令
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
3、确认备库已经是mount状态
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
MOUNTED dgtest
4、打到OPEN状态
SQL> ALTER DATABASE OPEN;
现在的主库切换几次日志文件，备库可以同步日志文件。
注：只有在备库OPEN完成切换，成为新主库之后，原主库的SWITCHOVER_STATUS状态才会由TO PRIMARY变为NOT ALLOWED
5、再查备库的角色，确认已经切换为主库
SQL> select CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from V$DATABASE;
CONTROL OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------- ---------------- ---------------- --------------------
CURRENT READ WRITE PRIMARY TO STANDBY
--------------------------------------------------------------
-----到此，主备库切换完毕，standby to primary过程已经完成-----
--------------------------------------------------------------
二、failover灾难切换
主库宕机，无法启动，紧急启用备库。直接在备库上操作，将备库转换为主库角色
备库上执行下面四条命令即可：
SQL > alter database recover managed standby database finish;
SQL > alter database commit to switchover to primary;
SQL > shutdown immediate;
SQL > startup;
注：此操作不可逆，ARCH从1开始计数，要重新做全备和部署DG
