ORACLE 12C DATAGUARD环境搭建和主从切换

千家信息网最后更新 2025年01月22日ORACLE 12C DATAGUARD环境搭建和主从切换环境说明：
目的：
（1）在linux 操作系统下，创建oracle 12c 单实例的dataguard环境
（2）测试dataguard主从切换
主库：10.50.115.12 （DB_UNIQUE_NAME=cdbtest,service_name=cdbtest,db_name=cdbtest）
从库：10.50.115.20 （DB_UNIQUE_NAME=cdbtest1,service_name=cdbtest,db_name=cdbtest）
备注：DB_UNIQUE_NAME不能一样

-------------------------------------------------------------------------------
一、创建oracle 12c 单实例的dataguard环境
主库：
mkdir -p /u01/app/oracle/arch/
chown oracle.oinstall /u01/app/oracle/arch/

1. 开启归档模式
sqlplus / as sysdba
startup mount
alter database archivelog;
alter database open;

2. 配置TNS,增加连接到从库的tns:
cat >> $ORACLE_HOME/network/admin/tnsnames.ora << EOF
cdbtest1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.115.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = cdbtest)
)
)
EOF

3. 修改主库的dataguard相关参数
alter system set standby_file_management='auto' scope=both;
alter system set log_archive_config='dg_config=(cdbtest,cdbtest1)' scope=both;
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdbtest' scope=both;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=cdbtest1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdbtest1' scope=both;
# 这些参数仅当主库切换成备库时生效
alter system set FAL_SERVER=cdbtest1 scope=both;
alter system set DB_FILE_NAME_CONVERT='cdbtest','cdbtest' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/cdbtest/','/u01/app/oracle/oradata/cdbtest/' scope=spfile;

4. 增加standby log
select member from v$logfile;
alter database add standby logfile '/u01/app/oracle/oradata/cdbtest/stdredo01.log' size 10M;
alter database add standby logfile '/u01/app/oracle/oradata/cdbtest/stdredo02.log' size 50M;
alter database add standby logfile '/u01/app/oracle/oradata/cdbtest/stdredo03.log' size 50M;

从库：
mkdir -p /u01/app/oracle/arch/
chown oracle.oinstall /u01/app/oracle/arch/

1. 创建与主库一样的数据库（主要目的是创建相关目录，数据文件随后会覆盖，不用创建pdb数据库）

2. 配置连接到主库的TNS:
cat >> $ORACLE_HOME/network/admin/tnsnames.ora << EOF
cdbtest_pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.50.115.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdbtest)
)
)
EOF

3. 创建静态监听器
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = cdbtest)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

4. 修改相关参数
alter system set log_archive_config='dg_config=(cdbtest,cdbtest1)' scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
alter system set DB_FILE_NAME_CONVERT='cdbtest','cdbtest' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/cdbtest/','/u01/app/oracle/oradata/cdbtest/' scope=spfile;
alter system set FAL_SERVER=cdbtest_pri scope=both;

alter system set log_archive_dest_state_1='enable' scope=both;
alter system set log_archive_dest_state_2='enable' scope=both;
alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cdbtest1' scope=both;
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=cdbtest_pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cdbtest'scope=both;

# 创建数据库后，db_unique_name将不能改
alter system set db_unique_name='cdbtest1' scope=spfile;
alter system set service_names=cdbtest scope=spfile;

5. 复制密码文件：
scp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwcdbtest 10.50.115.20://u01/app/oracle/product/12.1.0/dbhome_1/dbs/

6. 建pdb数据文件的目录(参考主库)
cd /u01/app/oracle/oradata/cdbtest
mkdir -p pdb1 pdb2

7. rman复制数据库
备库：
rman target sys/biostime123@cdbtest_pri auxiliary sys/biostime123@cdbtest1 nocatalog <duplicate target database for standby from active database nofilenamecheck;
EOF

8.启动日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


二、主从切换测试

主从切换：
主库：
1. 如果该列值为"TO STANDBY"则表示primary 数据库支持转换为standby
select switchover_status from v$database;

2. 首先将primary 转换为standby 的角色
alter database commit to switchover to physical standby;
（执行完后，数据库down掉）

3. 重启动到mount(原primary)
shutdown immediate
startup mount
select switchover_status from v$database;
说明：PRIMARY进行转换完毕后，查看状态会变成RECOVERY NEEDED;

从库：
备库切换成主库的操作
1、检查备库的状态
select switchover_status from v$database;
主库关闭前为"NOT ALLOWED"，关闭后，从库变为"TO_PRIMARY"

2. 确认没有问题后，可以进行切换转换standby 到primary 角色
alter database commit to switchover to primary;

3. 完成转换，打开新的primary 数据库
alter database open;

4.检查状态
select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;

5. 在原主库启动日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;