oracle12c主备搭建,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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31520497/viewspace-2152255/，如需转载，请注明出处，否则将追究法律责任。