oracle12c搭建dataguard

两边的数据库建议除了db_unique_name名字都一样

主库启动FORCE LOGGING

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

 


主库启动归档模式

SQL> alter system set log_archive_dest_1='location=/data/arch';

system altered.

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database open;

 

在主库添加 standby redo logfile，比redolog多一组，并且要和redolog大小一致

alter database add standby logfile '/data/app/oracle/oradata/oracle/stby_redo1.log' size 50M;
alter database add standby logfile '/data/app/oracle/oradata/oracle/stby_redo2.log' size 50M;
alter database add standby logfile '/data/app/oracle/oradata/oracle/stby_redo3.log' size 50M;
alter database add standby logfile '/data/app/oracle/oradata/oracle/stby_redo4.log' size 50M;


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/data/app/oracle/oradata/oracle/redo03.log
/data/app/oracle/oradata/oracle/redo02.log
/data/app/oracle/oradata/oracle/redo01.log
/data/app/oracle/oradata/oracle/stby_redo1.log
/data/app/oracle/oradata/oracle/stby_redo2.log
/data/app/oracle/oradata/oracle/stby_redo3.log
/data/app/oracle/oradata/oracle/stby_redo4.log


分别在主备库配置监听并启动
# listener.ora Network Configuration File: /data/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER = 
  (SID_LIST =
    (
      SID_DESC =
      (GLOBAL_DBNAME = oracle)
      (SID_NAME = oracle)
      (ORACLE_HOME = /data/app/oracle/product/12.2.0/dbhome_1) 
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST =173.32.18.70 )(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /data/app/oracle

 


分别在主备库配置tnsnames.ora

# tnsnames.ora Network Configuration File: /data/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

oracle_p =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 173.32.18.70)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )


oracle_s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 173.32.18.71)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )

 

 


在主库创建pfile 文件并修改pfile 内容

SQL> create pfile from spfile;

 

#primary

*.db_unique_name='oracle_p'

*.log_archive_config='dg_config=(oracle_p,oracle_s)'

*.log_archive_dest_1 = 'location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=oracle_p'

*.log_archive_dest_2= 'service=oracle_s LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=oracle_s'

#*.log_archive_dest_2= 'service=oracle_s LGWR affirm SYNC  valid_for=(online_logfiles, primary_role) db_unique_name=oracle_s' --对性能影响较大

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.fal_server='oracle_s'

*.fal_client='oracle_p'

*.standby_file_management='auto'

*.log_file_name_convert=('/data/app/oracle/oradata/oracle','/data/app/oracle/oradata/oracle')

*.db_file_name_convert=('/data/app/oracle/oradata/oracle','/data/app/oracle/oradata/oracle')


用spfile启动数据库
SQL> create spfile from pfile 

 

将主库的口令文件copy到备库
[oracle@cwxt-orac02 dbs]$ scp oracle@173.32.18.70:/data/app/oracle/product/12.2.0/dbhome_1/dbs/orapwnccdb ./

 

将主库的pfile拷贝到备库并修改


#standby

*.db_unique_name='oracle_s'

*.log_archive_config='dg_config=(oracle_p,oracle_s)'

*.log_archive_dest_1 = 'location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=oracle_s'

#*.log_archive_dest_2= 'service=oracle_p LGWR ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=oracle_p'    --异步传输

*.log_archive_dest_2= 'service=oracle_p LGWR affirm SYNC  valid_for=(online_logfiles, primary_role) db_unique_name=oracle_p'   

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.fal_server='oracle_p'

*.fal_client='oracle_s'

*.standby_file_management='auto'

*.log_file_name_convert=('/data/app/oracle/oradata/oracle','/data/app/oracle/oradata/oracle')

*.db_file_name_convert=('/data/app/oracle/oradata/oracle','/data/app/oracle/oradata/oracle')

 


用spfile 将备库启动到nomount 状态

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;

File created.

SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size 19248848 bytes
Variable Size 3422554416 bytes
Database Buffers 1.7985E+10 bytes
Redo Buffers 47857664 bytes

 

开始进行Active duplicate
[oracle@cwxt-orac02 admin]$ rman target sys/sys@oracle_p auxiliary sys/sys@oracle_s nocatalog

Recovery Manager: Release 12.2.0.1.0 - Production on Sat Dec 5 23:32:25 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORACLE (DBID=1894558455)
using target database control file instead of recovery catalog
connected to auxiliary database: ORACLE (not mounted)

RMAN>


RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 05-DEC-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1222 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/data/app/oracle/product/12.2.0/dbhome_1/dbs/orapwnccdb' auxiliary format
'/data/app/oracle/product/12.2.0/dbhome_1/dbs/orapwnccdb' ;
}
executing Memory Script

Starting backup at 05-DEC-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=374 device type=DISK
Finished backup at 05-DEC-20

contents of Memory Script:
{
restore clone from service 'oracle_p' standby controlfile;
}
executing Memory Script

Starting restore at 05-DEC-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/app/oracle/oradata/oracle/control01.ctl
output file name=/data/app/oracle/oradata/oracle/control02.ctl
Finished restore at 05-DEC-20

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/data/app/oracle/oradata/oracle/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/data/app/oracle/oradata/oracle/system01.dbf";
set newname for datafile 2 to
"/data/app/oracle/oradata/oracle/nnc_index01.dbf";
set newname for datafile 3 to
"/data/app/oracle/oradata/oracle/sysaux01.dbf";
set newname for datafile 4 to
"/data/app/oracle/oradata/oracle/undotbs01.dbf";
set newname for datafile 5 to
"/data/app/oracle/oradata/oracle/nnc_data01.dbf";
set newname for datafile 7 to
"/data/app/oracle/oradata/oracle/users01.dbf";
set newname for datafile 8 to
"/data/app/oracle/oradata/oracle/nnc_data01_02.dbf";
set newname for datafile 9 to
"/data/app/oracle/oradata/oracle/fxy_data01_01.dbf";
restore
from nonsparse from service
'oracle_p' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data/app/oracle/oradata/oracle/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 05-DEC-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/app/oracle/oradata/oracle/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data/app/oracle/oradata/oracle/nnc_index01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/app/oracle/oradata/oracle/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/app/oracle/oradata/oracle/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data/app/oracle/oradata/oracle/nnc_data01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /data/app/oracle/oradata/oracle/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /data/app/oracle/oradata/oracle/nnc_data01_02.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:05
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service oracle_p
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /data/app/oracle/oradata/oracle/fxy_data01_01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 05-DEC-20

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/nnc_index01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/nnc_data01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/nnc_data01_02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=8 STAMP=1058399339 file name=/data/app/oracle/oradata/oracle/fxy_data01_01.dbf
Finished Duplicate Db at 05-DEC-20


查看主库状态

SQL> select log_mode,open_mode ,database_role from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ WRITE PRIMARY


查看备库状态，备库是只读只能查询

SQL> select log_mode,open_mode ,database_role from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ ONLY PHYSICAL STANDBY


备库启用real-time apply

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

查看保护模式
SQL> select protection_mode,protection_level from v$database;

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

 

查看归档日志应用状态

SQL> Select sequence#,name,applied from v$archived_log;
SEQUENCE# NAME APPLIED
---------- -------------------------------------------------- ---------
1999 /data/arch/%ORACLE_SID%T_0001S00000019991021481017 YES
.ARC

2000 /data/arch/%ORACLE_SID%T_0001S00000020001021481017 YES
.ARC

1998 /data/arch/%ORACLE_SID%T_0001S00000019981021481017 YES
.ARC

2001 /data/arch/%ORACLE_SID%T_0001S00000020011021481017 YES
.ARC

2002 /data/arch/%ORACLE_SID%T_0001S00000020021021481017 YES
.ARC

2003 /data/arch/%ORACLE_SID%T_0001S00000020031021481017 YES
.ARC

2004 /data/arch/%ORACLE_SID%T_0001S00000020041021481017 YES
.ARC

2005 /data/arch/1_2005_1021481017.arc YES

8 rows selected.

 

 

 

dataguard --swithover 和failover的说明：

SWITCHOVER

Switchover是有计划的将primary切换为standby,standby切换为primary.在主库结束生产后，备库应用完所有主库archivelog或者redo log后进行切换，不丢失数据。

常用场景:

(1) 有计划的灾备切换演练，如测试灾备站点的可用性，客户端连接等，确保主库发生重大意外时，可以切换至备库正常运转。

(2) 主库长时间的维护升级,如OS升级，DB滚动升级，更换存储，更换OS硬件设备等。

(3) 切换有风险，一般不做切换。

FAILOVER

Failover通常在主库突发故障，短时间无法解决，考虑到备库的可用性，数据丢失的容忍度，迫切需要向外提供数据库服务时进行Failover.

Failover前，如果数据库没有操作在最大保护模式，可能发生数据丢失。

Failover前，需要将准备Failover的备库置于最大性能模式。

Failover前，尽量应用所有的主库重做数据，减少数据丢失。

Failover后，原主库将从DG配置中删除，如果原主库启用了Flashback,则在修复故障后，故障的数据库可恢复为新的standby数据库。

 

Oracle11.2之前，可以拷贝primary的online redo log到standby做recover,从Oracle11.2开始，Oracle提供了flush redo到standby的功能，当primary不能OPEN时，启动到mount状态，standby redo apply处于激活状态，执行以下语句flush redo到standby，以此来减少数据丢失：

SQL> alter system flush redo to target_db_name; 

 

具体切换过程

①switchover-----无损转换

1、检查primary数据库是否支持切换成standby

   SQL>Select switchover_status from v$database;

   如果支持则status状态为  To standby 或session active（当前有用户连接）

2、启动switchover

   SQL>alter database commit to switchover to standby/physical standby with session shutdown;

3、SQL>shutdown immediate

   SQL>startup mount

 

4、检查standby库是否支持切换

   SQL>Select switchover_status from v$database;

       如果支持则status状态为  To primary或session active（当前有用户连接）或switchover pending              （standby库没有启用redo应用）

       如果为switchover pending状态则需要执行启用redo日志应用：

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

5、转换角色到primary

   SQL>alter database commit to switchover to primary with session shutdown;

   SQL>alter database open;

 

②failover-----会丢失数据

执行failover后原primary库将不再是dataguard中的一部分，所以在执行failover之前，尽可能将原primary库中的redo文件（含联机重做日志文件和归档日志文件）都复制到standby库

1、如果待转换的standby库处于maximum protection模式，需要切换到maximum performence模式

SQL>alter database set standby database to maximum performence;

2、检查归档日志是否连续

   查询待转换standby库（原primary）的v$archive_gap，确认归档文件是否连续

SQL>select thread#,low_sequence#,high_sequence# from v$archive_gap;

如果有返回记录，则按照列出的记录号复制对应的归档日志到待转换的standby服务器

文件复制过去后在待转换的standby服务器上执行：

SQL>alter database register physical logfile ‘filespec1’;

3、检查归档文件是否完整

分别在两台服务器上执行:

SQL>select distinct thread#,max(sequence#)  over(partition by thread#) a from v$archive_log;

4、启动failover

SQL>alter database recover managed standby database finish force;

5、切换物理standby为primary

SQL>alter database commit to switchover to primary;

6、启动新的primary

如果当前库为mount则直接open，如果为read only，则要先shutdown再open。

 

posted @ 2020-12-06 22:14  lysheng  阅读(808)  评论(0)  编辑  收藏  举报