dg环境搭建v2

1 DATA GRARD 环境搭建 一、安装数据库软件 2 1.1主机名配置 .3 1.2监听配置 .4 1.3主库参数修改 .5 1.4备库参数修改 .7 二、使用 duplicate复制数据库 .8 2.1 复制数据库 8 2.2 查询主备库角色 8 2.3 数据同步测试 9 三、主备库切换 10 3.1 切换前准备 10 3.2 switchover 主动切换 11 3.2.1 主库切换成备库 .11 3.2.2 备库切换成主库 .13 四、failover 故障切换 .16 4.1 切换前准备 16 4.2 切换 17 4.3 failover后恢复主库 .17 五、切换物理备库为快照备库 18 六、切换快照备库为物理备库 20 七、切换物理备库为逻辑备库 21 八、逻辑备库与主库 switchover切换 .23 九、逻辑备库与主库 failover切换 .25 2 一、安装数据库软件 规划： （1 ）主库： IP：192.168.0.121 Hostname：db01 ORACLE_SID=orcl （2）备库： IP：192.168.0.122 Hostname：db02 ORACLE_SID=orcl Db01 中 ORACLE 用户的的环境变量 umask 022 export ORACLE_BASE=/u01/app/product export ORACLE_HOME=/u01/app/product/oracle export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH export ORACLE_SID=orcl export ORACLE_UNQNAME=orcl export ORACLE_OWNER=oracle export THREADS_FLAG=native export LD_LIBRARY_PATH=/u01/app/product/oracle/lib:/lib:/usr/lib export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib export PATH=/u01/app/product/oracle/bin:$PATH export NLS_LANG=american_america.ZHS16GBK export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data db02 中 oracle 用户的环境变量 umask 022 export ORACLE_BASE=/u01/app/product export ORACLE_HOME=/u01/app/product/oracle export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH export ORACLE_SID=orcl export ORACLE_UNQNAME=orcl 3 export ORACLE_OWNER=oracle export THREADS_FLAG=native export LD_LIBRARY_PATH=/u01/app/product/oracle/lib:/lib:/usr/lib export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib export PATH=/u01/app/product/oracle/bin:$PATH export NLS_LANG=american_america.ZHS16GBK export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data 1.1主机名配置 分别在两个库相互绑定主机名和 IP 主： 备： 4 1.2监听配置 主库 listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = db01) (ORACLE_HOME = /u01/app/oracle/product) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = db01_orcldg) (ORACLE_HOME = /u01/app/oracle/product) (SID_NAME = orcl) ) ) tnsname.ora ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db01) ) ) ORCLDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db02) ) ) 备库 listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = 5 (GLOBAL_DBNAME = db02) (ORACLE_HOME = /u01/app/oracle/product) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = db02_orcl) (ORACLE_HOME = /u01/app/oracle/product) (SID_NAME = orcl ) ) tnsname.ora ORCLDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db02) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db01) ) ) 1.3主库参数修改 （1 ） 开启 force_logging SQL startup mount; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL alter database force logging; 6 Database altered. SQL alter database open; Database altered. SQL select force_logging from v$database; FOR - YES （2 ） 修改 log_archive_config 参数 SQL show parameter log_archive_config; NAME TYPE VALUE - - - log_archive_config string SQL alter system set log_archive_config=dg_config=(db01,db02); System altered. SQL show parameter log_archive_config; NAME TYPE VALUE - - - log_archive_config string dg_config=(db01,db02) SQL （3 ） 修改归档目录参数 SQLalter system set log_archive_dest_1=location=/u01/app/oracle/arch db_unique_name=db01; System altered. SQL alter system set log_archive_dest_2=service=db02 db_unique_name=db02; System altered. SQL 7 1.4备库参数修改 （1 ） 配置 DG 的 fal_server 和 fal_client 这里在备库中设置的，不考虑主库转变为 standby 角色。 SQL alter system set fal_server=db01; System altered. SQL alter system set fal_client=db02; System altered. SQL （2 ） 修改 log_archive_config 参数 SQL alter system set log_archive_config=dg_config=(db01,db02); System altered. SQL （3 ） 设置 standby_file_management 为自动 SQL show parameter standby_file_management; NAME TYPE VALUE - - - standby_file_management string MANUAL SQL alter system set standby_file_management=auto; System altered. SQL show parameter standby_file_management; NAME TYPE VALUE - - - standby_file_management string auto SQL （4 ）修改归档存放路径 SQLalter system set log_archive_dest_1=location=/u01/app/oracle/arch db_unique_name=db02; System altered. 8 二、使用 duplicate复制数据库 2.1 复制数据库 oracledb02 oracle$rman target sys/oracledb01 auxiliary sys/oracledb02 RMANduplicate target database for standby nofilenamecheck from active database; 2.2 查询主备库角色 9 2.3 数据同步测试 主库插入数据 查询备库的数据，此时还没有同步过来 主库手工切换一次日志 再次查询备库，此时数据已同步。 10 三、主备库切换 3.1 切换前准备 （1 ）查看主库状态 SQL select name,open_mode,protection_mode,force_logging,database_role from v$database; NAME OPEN_MODE PROTECTION_MODE FOR DATABASE_ROLE - - - - - ORCL READ WRITE MAXIMUM PERFORMANCE YES PRIMARY SQL （2 ）查看备库状态 SQL select name,open_mode,protection_mode,force_logging,database_role from v$database; NAME OPEN_MODE PROTECTION_MODE FOR DATABASE_ROLE - - - - - ORCL READ ONLY MAXIMUM PERFORMANCE YES PHYSICAL STANDBY SQL （3 ）检查主备库归档状态 主库 11 备库 设置备库的 log_archive_dest_2 参数 alter system set log_archive_dest_2= service db01 valid_for=(online_logfiles,primary_role) db_unique_name=db01; 3.2 switchover 主动切换 3.2.1 主库切换成备库 （1 ）查看主库是否支持切换备库操作 12 分析:这里的 switchover_status 状态为 failed destination，表示备库处于错误状态。To standby 状态才表示支持主库切换成备库。 解决办法：发现是主备库监听没开启。启动监听后再次查询 分析：再次查询后显示状态为 sessions active，表示当前有会话连接到数据库 解决办法：杀掉连接会话，观察状态 再次查询主库状态为 to standby 了，表示据库已经准备好切换到物理备库或者逻辑备库 （2 ）切换 SQL alter database commit to switchover to physical standby with session shutdown; Database altered. SQL startup nomount; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes SQL alter database mount standby database; Database altered. SQL alter database recover managed standby database disconnect from session; Database altered. SQL select name,open_mode,database_role,switchover_status from v$database; NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS - - - - ORCL MOUNTED PHYSICAL STANDBY RECOVERY NEEDED SQL SQL shutdown immediate; 13 ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL startup; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. Database opened. SQL select name,open_mode,database_role,switchover_status from v$database; NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS - - - - ORCL READ ONLY PHYSICAL STANDBY NOT ALLOWED SQL 3.2.2 备库切换成主库 （1 ）查看备库 switchover_status 的状态，正常为 to primary SQL select name,open_mode,database_role,switchover_status from v$database; NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS - - - - ORCL READ ONLY PHYSICAL STANDBY NOT ALLOWED 分析：查询显示备库的 switchover_status 为 not allowed，即没有接受到主库的切换请求 解决办法：启动日志应用 SQL alter database recover managed standby database disconnect from session ; Database altered. 再次查询，状态显示为 to primary，即数据库已经准备好切换到主库角色 SQL select name,open_mode,database_role,switchover_status from v$database; NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS - - - - ORCL READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY SQL （2 ）备库切换 SQL alter database commit to switchover to primary with session shutdown; Database altered. SQL select open_mode from v$database; 14 OPEN_MODE - MOUNTED SQL startup; ORA-01081: cannot start already-running ORACLE - shut it down first SQL shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL startup; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. Database opened. SQL select name,open_mode,database_role,switchover_status from v$database; NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS - - - - ORCL READ WRITE PRIMARY SESSIONS ACTIVE SQL 这里测试了下数据同步，主库（db02）插入一条数据并手工切换了次日志，备库（db01） 数据没有同步 15 备库日志如下： 分析：切换后的备库没有开启日志应用 解决办法：开启日志应用 16 查看备库的日志，也不再报错 至此，switchover 切换成功 四、failover 故障切换 4.1 切换前准备 （1 ）检查主库状态 17 4.2 切换 （1 ）停止主库 （2 ） 备库执行以下命令切换 SQL alter database recover managed standby database cancel; Database altered. SQL alter database recover managed standby database finish; Database altered. SQL alter database commit to switchover to primary; Database altered. SQL alter database open; Database altered. SQL select open_mode,database_role from v$database; 至此，failover 切换成功 4.3 failover后恢复主库 （1 ）查看主库 SCN 18 （2 ）闪回原来的主库到故障切换时的 scn，再转换为备库，最后通过应用日志恢复故障期 间的数据 SQL startup mount; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL FLASHBACK DATABASE TO SCN 1161002; Flashback complete. SQL ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered. SQL SHUTDOWN IMMEDIATE; ORA-01507: database not mounted ORACLE instance shut down. SQL startup; ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. Database opened. SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL （3 ）查看备库状态 SQL select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE - - - ORCL READ ONLY WITH APPLY PHYSICAL STANDBY SQL 五、切换物理备库为快照备库 （1 ）查看备库状态 19 （2 ）停止日志应用 SQL alter database recover managed standby database cancel; （3 ）转换备库为快照库 SQLalter database convert to snapshot standby; （3 ）打开快照库 可以看到，数据库状态为 read write （4 ）测试 在快照库中的 scott.test 中插入一条数据，查看主库数据是否有变化 备库 20 主库 可以看到，主库不受任何影响 此时查看 v$restore_point 视图可以看到一个还原点 SQLselect name,storage_size from v$restore_point; 六、切换快照备库为物理备库 （1 ）启动数据库到 mount （2 ）转换快照库为物理备库 SQLalter database convert to physical standby; （3 ）重启数据库，查看备库状态 21 （4 ）查看还原点和数据 可以看到，还原点已被删除，而且数据还原到修改之前 （5 ）应用备库日志 SQL alter database recover managed standby database using current logfile disconnect from session; 七、切换物理备库为逻辑备库 配置逻辑备库需要先配置 DG 物理备库 （1 ）查看备库状态 Select open_mode,database_role from v$database; （2 ）停止备库日志应用 SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; （3 ）修改主库归档路径 22 SQL alter system set log_archive_dest_3=LOCATION=/u01/app/oracle/arch_logical valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=db01; SQL alter system set log_archive_dest_state_3