Oracle11g DataGuard不停机部署
程序员文章站
2022-03-08 16:04:54
Oracle DG部署（RMAN方式） 1.环境介绍 2.DG部署 2.1.建立主库orcl 2.2.主库开启归档主库开启归档并设置强制日志 force logging--关闭数据库SQL> shutdown immediate--以mount模式启动SQL> startup mount--切换到归档模式SQL> alter database archivelog;--开启强制日志SQL> alter databas...
Oracle DG部署（RMAN方式）
1.环境介绍
2.DG部署
2.1.建立主库orcl
2.2.主库开启归档
主库开启归档并设置强制日志 force logging
--关闭数据库
SQL> shutdown immediate
--以mount模式启动
SQL> startup mount
--切换到归档模式
SQL> alter database archivelog;
--开启强制日志
SQL> alter database force logging;
--打开数据库
SQL> alter database open;
--查看归档
SQL> archive log list;
--查看是否为强制日志
SQL> select force_logging from v$database;
（先备份再添加Standby Redo Log）
2.4 备份数据库
run {
allocate channel d1 type disk ;
allocate channel d2 type disk ;
backup as compressed backupset database plus archivelog format '/u01/app/backup/db_%d_%s_%p_%I.bak' ;
sql 'alter system archive log current';
backup as compressed backupset archivelog all format '/u01/app/backup/log_%d_%s_%p_%I.bak';
backup current controlfile format '/u01/app/backup/ctl_%d_%s_%p_%I.bak';
crosscheck backup;
}
2.5 开启强制日志
--开启强制日志
SQL> alter database force logging;
--查看是否为强制日志
SQL> select force_logging from v$database;
2.6 从主库创建pfile文件
创建pfile文件, 默认路径为$ORACLE_HOME/dbs，此处为/u01/app/oracle/product/11.2.0/dbhome_1/dbs/，在sqlplus里执行以下命令
SQL> create pfile from spfile;
将主库的pfile复制到备库/u01/app/oracle/product/11.2.0/dbhome_1/dbs/下
cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp initorcl.ora db12:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
2.7 主库上为备库建立控制文件
alter database create standby controlfile as '/u01/backup/control01.ctl' reuse;
2.8复制主库的密码文件到备库
将密码文件orapworcl复制到备库的/u01/app/oracle/product/11.2.0/dbhome_1/dbs/下
cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp orapworcl db12:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
2.9创建备库相应的目录结构
使用oracle用户创建目录，避免权限问题
2.10配置主库和备库的监听（不停机可暂时不配）
主库：
在主库监听listener.ora中添加
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = orcl)
)
)
GLOBAL_DBNAME参数格式：<db_unique_name>_DGMGRL.<db_domain> 的连接，并且db_unique_name 大小写敏感。
备库：
在备库库监听listener.ora中添加
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl2_DGMGRL)
(ORACLE_HOME = /u02/oracle/product/11.2.0.4/db_1)
(SID_NAME = orcl)
)
)
重载监听
lsnrctl reload
2.11 配置主库和备库的网络服务名
主库：
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-dg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(UR=A) 静态链接数据库
)
)
备库：
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-dg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-dg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(UR=A)
)
)
测试监听，分别在主备库上都执行：
tnsping orcl
tnsping orcl2
主备测试连接(配置后重启主备两台机器的数据库)
--主
[oracle@DB196 MPCDB]$ sqlplus sys/MPCDB@PL as sysdba
[oracle@DB196 MPCDB]$ sqlplus sys/MPCDB@SL as sysdba
--备
[oracle@DB197 admin]$ sqlplus sys/MPCDB@PL as sysdba
[oracle@DB197 admin]$ sqlplus sys/MPCDB@SL as sysdba
2.12设置主备库初始化参数
Set ADG Parameter（主库配置）
#连接自身的tnsname里面的服务名
SQL> alter system set job_queue_processes=10 scope=spfile;
SQL> alter system set db_unique_name='MPCDB196' scope=spfile;
SQL> alter system set local_listener='PL' scope=spfile;
SQL> alter system set global_names=true scope=both;
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO MPCDB;
--db_file_name_convert ：主库数据文件路径，备库数据文件路径
SQL> alter system set db_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;
--log_file_name_convert：主库的日志文件路径，备库的日志文件路径
SQL> alter system set log_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;
--配置tnsnames.ora网络服务名，fal_server拷贝丢失的归档文件到这里）
SQL> alter system set fal_client='PL' scope=spfile;
--（配置tnsnames.ora网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）
SQL> alter system set fal_server='SL' scope=spfile;
--启动db接受或发送redo data，包括所有库的db_unique_name
SQL> alter system set log_archive_config = 'DG_CONFIG=(MPCDB196,MPCDB197)' scope=spfile;
#SQL> alter system set log_archive_dest_1='location=/u03/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB196' scope=spfile;
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB196' scope=both;
SQL> alter system set log_archive_dest_2='service=SL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MPCDB197' scope=spfile;
--SQL> alter system set log_archive_format='ARC_%t_%S_%r.arc' scope=spfile;
#还原默认值（未改过则无需执行）
SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;
SQL> alter system set standby_file_management='AUTO' scope=spfile;
SQL> alter system set service_names='MPCDB' scope=spfile;
SQL> alter system set log_archive_max_processes=4 scope=spfile;
Set ADG Parameter（备库配置）
#连接自身的tnsname里面的服务名
SQL> alter system set job_queue_processes=10 scope=spfile;
SQL> alter system set db_unique_name='MPCDB197' scope=spfile;
SQL> alter system set global_names=true scope=both;
SQL> alter system set local_listener='SL' scope=spfile;
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO MPCDB;
--db_file_name_convert ：主库数据文件路径，备库数据文件路径
SQL> alter system set db_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;
-- log_file_name_convert：主库的日志文件路径，备库的日志文件路径
SQL> alter system set log_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;
--（配置tnsnames.ora网络服务名，fal_server拷贝丢失的归档文件到这里）
SQL> alter system set fal_client='SL' scope=spfile;
--（配置tnsnames.ora网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）
SQL> alter system set fal_server='PL' scope=spfile;
--启动db接受或发送redo data，包括所有库的db_unique_name
SQL> alter system set log_archive_config = 'DG_CONFIG=(MPCDB197,MPCDB196)' scope=spfile;
#SQL> alter system set log_archive_dest_1='location=/u03/fast_recovery_area/MPCDB/MPCDB/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB197' scope=spfile;
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB197' scope=both;
SQL> alter system set log_archive_dest_2='service=PL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MPCDB196' scope=spfile;
SQL> alter system set log_archive_format='ARC_%t_%S_%r.arc' scope=spfile;
#还原默认值（未改过则无需执行）
SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;
SQL> alter system set standby_file_management='AUTO' scope=spfile;
SQL> alter system set service_names='MPCDB' scope=spfile;
SQL> alter system set log_archive_max_processes=4 scope=spfile;
设置主库初始化参数
编辑/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora文件，追加
cat >> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora << "EOF"
*.db_unique_name='orcl'
*.fal_server='orcl2'
*.log_archive_config='dg_config=(orcl1,orcl2)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles, all_roles) db_unique_name=orcl11'
*.log_archive_dest_2='service=orcl12 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl12'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.standby_file_management='AUTO'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
EOF
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl2)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl2 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
ALTER SYSTEM SET FAL_SERVER=orcl;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u02/oracle/oradata/orcl' scope=spfile;主库数据文件路径，备库数据文件路径
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u02/oracle/oradata/orcl' scope=spfile;主库的日志文件路径，备库的日志文件路径
--（配置tnsnames.ora网络服务名，fal_server拷贝丢失的归档文件到这里）
SQL> alter system set fal_client='orcl' scope=spfile;
--（配置tnsnames.ora网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）
SQL> alter system set fal_server='orcl2' scope=spfile;
设置备库初始化参数
添加备库参数文件以下参数
*.log_archive_config='dg_config=(orcl,orcl2)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl2'
*.log_archive_dest_2='SERVICE=orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orcl
*.db_unique_name=orcl2
*.log_file_name_convert='/u01/oracle/oradata/orcl','/u02/oracle/oradata/orcl'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' 修改空服之文件路径
--（配置tnsnames.ora网络服务名，fal_server拷贝丢失的归档文件到这里）
SQL> alter system set fal_client='orcl2' scope=spfile;
--（配置tnsnames.ora网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）
SQL> alter system set fal_server='orcl1' scope=spfile;
2.13 创建备库并启动
创建备库的spfile文件，启动备库到nomount模式
SQL> startup nomount pfile='/tmp/initorcl.ora';
SQL> create spfile from pfile;
SQL> startup nomount（在有控制文件的情况下可以直接启动到mount状态）
SQL> startup mount
2.14 RMAN复制主库到备库
重建redo前，先修改alter system set standby_file_management=manual；再dropREDO文件
select group#,members,bytes/1024/1024,status from v$log;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
主备库：
alter database add logfile group 1 ('/u02/oracle/oradata/orcl/redo01.log') size 50M;
alter database add logfile group 2 ('/u02/oracle/oradata/orcl/redo02.log') size 50M;
alter database add logfile group 3 ('/u02/oracle/oradata/orcl/redo03.log') size 50M;
alter database rename file '/u01/app/oracle/oradata/orcl/standby21.log' to '/u02/oracle/oradata/orcl/standby21.log
alter database rename file '/u01/app/oracle/oradata/orcl/standby22.log' to '/u02/oracle/oradata/orcl/standby22.log';
alter database rename file '/u01/app/oracle/oradata/orcl/standby23.log' to '/u02/oracle/oradata/orcl/standby23.log';
alter system switch logfile;
2.14.1在目录相同的情况下：
在主库通过Rman Duplicate创建备库,在主库上执行如下命令
rman target sys/123456@orcl(主) auxiliary sys/123456@orcl2（备） nocatalog;
rman target sys/111111@orcl auxiliary sys/111111@orcl2 nocatalog;
使用RMAN的duplicate命令进行复制，两边目录结构相同，需要添加nofilenamecheck参数
RMAN> duplicate target database for standby from active database nofilenamecheck;
复制成功后，备库自动被加载为mount模式，进入sqlplus查看
SQL> select status from v$instance;
2.14.1在目录不同的情况下：
进入rman
rman target/
restore standby controlfile from "/backup/inc.ctl";
select file#,name from v$datafile;查询数据文件位置。
catalog start with '/u02/oracle/backup';设置catalog路径
crosscheck backup;
根据实际的服务器的存储情况，重定义数据文件位置，并执行store操作
RUN {
SET NEWNAME FOR DATAFILE 1 to '/u02/oracle/oradata/orcl/system01.dbf';
SET NEWNAME FOR DATAFILE 2 to '/u02/oracle/oradata/orcl/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 to '/u02/oracle/oradata/orcl/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 to '/u02/oracle/oradata/orcl/users01.dbf';
SET NEWNAME FOR DATAFILE 5 to '/u02/oracle/oradata/orcl/example01.dbf';
restore database;
recover database;
switch datafile all;
}
备库redolog重建步骤：
SQL> alter system set standby_file_management=manual;
System altered.
SQL> alter database recover managed standby database cancel;
Database altered..
SQL> select group#,status,bytes/1024/1024 from v$log;
redolog 8 是当前正在使用的日志 所以不能直接删除，由于是备库 也不能切换日志，所以可以修改日志路径：
alter database rename file '/crbank/dbs/oradata/dbs/redo12.log' to '/crbank/dbs/data/oradata/dbs/redolog12.log'; --跟换路径
SQL> alter database add logfile group 1 '/crbank/dbs/data/oradata/dbs/redolog01.log' size 256m; --新增另外一组日志
alter system set standby_file_management=auto;
alter database recover managed standby database using current logfile disconnect;
select process,status,thread#,sequence#,block#,blocks from v$managed_standby where process!='ARCH';
2.15 主库添加Standby Redo Log（先备份再添加Standby Redo Log）
--查看Redo和Standby Redo
SQL> alter database force logging;
SQL> select * from v$logfile;
--仅仅显示Online Redo，不显示Standby Redo
SQL> select * from v$log;
--新增一组大小为500M的Standby Redo，这里的group号不得与Online redo重复
SQL> alter database add standby logfile group 21 '/u01/app/oracle/oradata/orcl/standby21.log' size 50M;
SQL> alter database add standby logfile group 22 '/u01/app/oracle/oradata/orcl/standby22.log' size 50M;
SQL> alter database add standby logfile group 23 '/u01/app/oracle/oradata/orcl/standby23.log' size 50M;
SQL> alter database add standby logfile group 24 '/u01/app/oracle/oradata/orcl/standby24.log' size 50M;
alter database drop standby logfile group 21;
SQL> alter database add standby logfile group 21 '/u02/oracle/oradata/orcl/standby21.log' size 50M;
SQL> alter database add standby logfile group 22 '/u02/oracle/oradata/orcl/standby22.log' size 50M;
SQL> alter database add standby logfile group 23 '/u02/oracle/oradata/orcl/standby23.log' size 50M;
SQL> alter database add standby logfile group 24 '/u02/oracle/oradata/orcl/standby24.log' size 50M;
2.13.在备库开启实时日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
2.14.主备库角色状态查询
SQL> select switchover_status,database_role from v$database;
--主库显示：TO STANDBY/PRIMARY，如果显示SESSION ACTIVE表示还有活动的会话，需要关闭活动的会话再检查
--备库显示：NOT ALLOWED/PHYSICAL STANDBY
3.测试DG
3.1.执行日志切换测试
在主库端切换归档，在备库检查是否也发生了切换
主库上执行日志切换
SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;
备库上查看，日志的sequence号也跟着变了
SQL> archive log list;
3.2.查看备库启动的DG进程
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 23 CLOSING
ARCH ARCH 0 CONNECTED //归档进程
ARCH ARCH 21 CLOSING
ARCH ARCH 0 CONNECTED
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 24 IDLE //归档传输进程
RFS UNKNOWN 0 IDLE
MRP0 N/A 24 APPLYING_LOG //日志应用进程
9 rows selected.
3.3.查看数据库的保护模式
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
3.4.查看DG的日志信息
SQL> select * from v$dataguard_status;
3.5.Open Read Only Standby数据库
以只读方式打开备库，并开启实时日志应用
shutdown immediate
SQL> startup
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
SQL> select process,client_process,sequence#,status from v$managed_standby;
SQL> alter database recover managed standby database using current logfile disconnect from session;
3.6.解锁scott用户并添加数据，验证是否同步
在主库解锁scott用户并创建测试表，插入10000行数据。
set line 200
SQL> select username,default_tablespace,account_status from dba_users where username='SCOTT';
SQL> alter user scott account unlock;
SQL> conn scott/tiger;
SQL> show user
SQL> create table test001 (id number(10),name varchar2(20));
SQL> begin
for i in 1..10000 loop
insert into test001 values (1,'ww');
end loop;
end;
/
SQL> commit;
standby端查询scott用户是否解锁，以及test001表是否创建并且插入了10000行数据
SQL> conn scott/tiger;
SQL> select * from tab;
SQL> select count(*) from test001;
4.DG三种模式
4.1.最大性能模式max performance-默认
这种保护模式（默认）提供了可能的*别的数据保护，而不影响主数据库的性能。这是通过允许事务在恢复该事务所需重做数据在写到本地联机重做日志后立即提交而实现的。主数据库的重做数据流也写到至少一个备数据库，但是那个重做流相对于创建重做数据的事务是异步写的。
当所用的网络连接有足够的带宽，这种模式提供了近似于最大可用性模式的数据保护级别，并且对主数据库性能的影响最小。
4.2.最大可用性模式max availability
这种保护模式提供了可能的*别的数据保护，而不用与主数据库的可用性相折衷。与最大保护模式相同，在恢复事务所需的重做写到本地联机重做日志和至少一个事务一致性备数据库上的备重做日志之前，事务将不会提交。与最大保护模式不同的是，如果故障导致主数据库无法写重做流到异地备重做日志时，主数据库不会关闭。替代地，主数据库以最大性能模式运行直到故障消除，并且解决所有重做日志文件中的中断。当所有中断解决之后，主数据库自动继续以最大可用性模式运行。
这种模式确保如果主数据库故障，但是只有当第二次故障没有阻止完整的重做数据集从主数据库发送到至少一个备数据库时，不发生数据丢失。
4.3.最大保护模式max protection
这种保护模式确保如果主数据库故障不会发生数据丢失。要提供这种级别的保护，恢复每个事务所需的重做数据必须在事务提交之前同时写到本地联机重做日志和至少一个备数据库上的备重做日志。要确保不发生数据丢失，如果故障导致主数据库无法写重做流到至少一个事务一致性备数据库的备重做日志时，主数据库会关闭。
4.4.查询当前模式
SQL> select protection_mode,protection_level from v$database;
5.DG切换测试
5.1.DG switchover 切换测试
db11-orcl：主库------>备库
db12-orcl：备库------>主库
主备库角色状态查询
SQL> select switchover_status,database_role,open_mode from v$database;
--db11-orcl显示：TO STANDBY/PRIMARY，如果显示SESSION ACTIVE表示还有活动的会话，需要关闭会话再检查
--db12-orcl显示：NOT ALLOWED/PHYSICAL STANDBY
db11-orcl切换到备库
SQL> alter database commit to switchover to physical standby;
SQL> alter database commit to switchover to physical standby with session shutdown;
--如果状态显示SESSION ACTIVE，在切换的时候可以指定with session shutdown 子句强制关闭活动的会话。
SQL> shutdown immediate
SQL> startup mount
db12-orcl切换到主库
SQL> alter database commit to switchover to primary;
SQL> alter database open;
db11-orcl执行APPLY LOG命令
--启用mount状态下的APPLY LOG
SQL> alter database recover managed standby database disconnect from session;
--启用open状态（READ ONLY WITH APPLY）下的APPLY LOG
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
SQL> select switchover_status,database_role,open_mode from v$database;
5.2.DG failover 切换测试
db11-orcl：主库------>崩溃
db12-orcl：备库------>主库
主备库角色状态查询
SQL> select switchover_status,database_role,open_mode from v$database;
db11-orcl通过shutdown abort方式人工模拟主库崩溃，直接关闭
SQL> select open_mode from v$database;
SQL> shutdown abort
SQL> startup mount
SQL> alter system flush redo to 'orcl12';
db12-orcl执行如下操作切换为主库
SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;
--如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在备库，要进行关闭apply和结束应用动作。
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish;
SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL> select open_mode, switchover_status from v$database;
5.3.DG failover后重建DG
db11-orcl：崩溃------>备库
db12-orcl：主库------>主库（保持主库状态不变）
db12-orcl主库角色状态查询
SQL> select switchover_status,database_role,open_mode from v$database;
db12-orcl主库创建备库控制文件
mkdir -p /u01/bak/
SQL> alter database create standby controlfile as '/u01/bak/control01.ctl';
将备库控制文件拷贝至db11
scp ezdb12:/u01/bak/control01.ctl /u01/app/oracle/oradata/orcl/control01.ctl
cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/
mv /u01/app/oracle/fast_recovery_area/orcl/control01.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
恢复对应数据文件至db11，并启动db11-orcl到mount状态，应用APPLY模式
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;
启动db11-orcl至OPEN状态，并应用APPLY REDO模式
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
6.开启和关闭DataGuard的流程
6.1.关闭
关闭主库
SQL> shutdown immediate
关闭主库监听
lsnrctl stop
查询备库是否正在执行重做应用或实时应用。如果MRP0或MRP进程存在，则备库正在应用重做。
SQL> select process, status from v$managed_standby;
如果重做应用程序正在运行，停止备库的Redo日志的应用
SQL> alter database recover managed standby database cancel;
关闭备库
SQL> shutdown immediate
关闭备库监听
lsnrctl stop
6.2.开启
将standby数据库开启至只读模式(用于primary非常忙时，可以在standby跑一些报表)
$sqlplus “/as sysdba”
SQL>startup mount
SQL>alter database open read only;
将只读模式standby数据库切换至管理模式
$sqlplus “/as sysdba”
SQL>alter database recover managed standby database disconnect from session;
将管理模式的standby数据库切换至只读模式
$sqlplus “/as sysdba”
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
启动备库监听
lsnrctl start
启动备库到mount状态
SQL> startup mount;
开启备库的Redo日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
启动主库监听
lsnrctl start
启动主库
SQL> startup;
检查日志情况的SQL语句
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
一、准备工作
1.主备库监听分别添加数据库服务
orcl_DGMGRL或orcl2_DGMGRL：
cd /u01/oracle/orcl/network/admin
在主库监听listener.ora中添加
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /u01/oracle/orcl)
(SID_NAME = orcl)
)
GLOBAL_DBNAME参数格式：<db_unique_name>_DGMGRL.<db_domain> 的连接，并且db_unique_name 大小写敏感。
在备库库监听listener.ora中添加
(SID_DESC =
(GLOBAL_DBNAME = orcl2_DGMGRL)
(ORACLE_HOME = /u01/oracle/orcl)
(SID_NAME = orcl)
)
2.主备库分别重新加载监听：
lsnrctl reload
3.测试监听，分别在主备库上都执行：
tnsping orcl
tnsping orcl2
4.主备数据库修改参数local_listener:
主库SQL> alter system set local_listener='orcl'; --orcl为监听服务名
备库SQL> alter system set local_listener='orcl2'; --orcl2为监听服务名
5.配置主库的TNSNAMES.ORA
[oracle@dg1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/OraDb11g_home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCLDG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCLDG2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl2)
(UR=A)
)
)
6.并拷贝主库的文本参数、口令文件、tnsnames文件、rman备份、standby控制文件到备库
6.1rman备份主库，利用当晚的rman备份即可，我的rman备份脚本如下：
#!/bin/bash
rman target / <<EOF
run {
allocate channel c1 type disk maxpiecesize=20g;
allocate channel c2 type disk maxpiecesize=20g;
backup database format '/u01/backup/db_%d_%s_%p_%u_%T.dbf';
sql "alter system archive log current";
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
backup archivelog all format '/u01/backup/arc_%d_%u_%T.arc' delete all input;
backup current controlfile format '/u01/backup/ctf_%d_%u_%T.ctl';
release channel c1;
release channel c2;
}
EOF
6.2主库上为备库建立控制文件
alter database create standby controlfile as '/u01/standby.ctl' reuse;
7.主/备库建立归档文件路径
8.在主/备库文本参数文件修改或添加以下内容
主库：
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl2)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
ALTER SYSTEM SET FAL_SERVER=orcl;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;
备库参数文件：
#DG CONFIG
*.log_archive_config='dg_config=(orcl,orcl2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl2'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orcl
*.db_unique_name=orcl2
*.log_file_name_convert='/u01/oracle/oradata/orcl/','/u01/oracle/oradata/orcl/'
db_unique_name=orcl2
log_archive_config='dg_config=(orcl,orcl2)'
log_archive_dest_1='location=/u01/app/oracle/oradata/arch valid_for=(online_logfiles,all_roles) db_unique_name=orcl2'
log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
log_archive_max_processes=6
fal_server=orcl
fal_client=orcl2
standby_file_management=auto
log_file_name_convert='/u01/oracle/oradata/orcl/','/u01/oracle/oradata/orcl/' --避免新备库的redo放到闪回区中
9.备库建立spfile
create spfile from pfile;
二、搭建DG
主库配置：
1.修改主库参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl2)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
ALTER SYSTEM SET FAL_SERVER=orcl;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl/' scope=spfile;
备库配置：
1.修改备库参数
vi initoreo.ora
*.log_archive_config='dg_config=(orcl,orcl2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/arch LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl2'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orcl
*.db_unique_name=orcl2
*.log_file_name_convert='/u01/oracle/oradata/orcl/','/u01/oracle/oradata/orcl/'
2.启动备库监听
[oracle@dg2 ~]$ lsnrctl start
3.建立备库控制文件
4.rman回复备库
5.将主库的LOG_ARCHIVE_DEST_STATE_2设置为ENABLE
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENJABLE';
6.添加standby redo log
#主库增加 STANDBY LOGFILE
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/sredo04.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcl/sredo05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcl/sredo06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/orcl/sredo07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u01/app/oracle/oradata/orcl/sredo08.log' size 50M;
#备库增加STANDBY LOGFILE
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/sredo04.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcl/sredo05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcl/sredo06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/orcl/sredo07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 '/u01/app/oracle/oradata/orcl/sredo08.log' size 50M;
7.开启active dataguard
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
本文地址：https://blog.csdn.net/qq_41944882/article/details/110679184
下一篇： 查找MySQL查询语句被阻塞阻塞原因