19c ADG部署

发布时间：2020-02-26 12:18:03 作者：冰蓝冰冷
来源：网络阅读：409

因客户需要部署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

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

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

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#)

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

备库

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

MAX(SEQUENCE#)

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

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(*)

----------

若有多于一个会话则：

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

19c ADG部署

相关阅读