主库启动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 = ( ID_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。