Oracle搭建DG
----------------------------主库打开归档模式、打开强制写入日志 shutdown immediate; ----------------------------修改归档位置 mkdir -p /data/oracle/oradata/zsha/arch/--创建归档目录 startup mount; alter system set log_archive_dest_1='location=/data/oracle/oradata/zsha/arch/'; alter database archivelog; alter database open; ----------------------------强制写入日志在open状态也可以执行 alter database force logging; select log_mode, force_logging from v$database;--查看是否开启强制日志 ----------------------------查看日志文件 select * from v$log; select * from v$logfile; ----------------------------主库添加standby日志文件比redo多一个文件创建的组不要和redo日志组相同 standby日志和redo日志的大小一致 alter database add standby logfile group 14 '/data/oracle/oradata/zshdb/standby14.log' size 50M; alter database add standby logfile group 15 '/data/oracle/oradata/zshdb/standby15.log' size 50M; alter database add standby logfile group 16 '/data/oracle/oradata/zshdb/standby16.log' size 50M; alter database add standby logfile group 17 '/data/oracle/oradata/zshdb/standby17.log' size 50M; alter database add standby logfile group 18 '/data/oracle/oradata/zshdb/standby18.log' size 50M; --清除日志组(创建日志有问题时执行) alter database clear logfile group 1; alter database clear logfile group 2; alter database clear logfile group 3; alter database clear logfile group 14; alter database clear logfile group 15; alter database clear logfile group 16; alter database clear logfile group 17; alter database clear logfile group 5; alter database clear logfile group 6; alter database clear logfile group 11; alter database clear logfile group 12; alter database clear logfile group 13; alter database clear logfile group 24; alter database clear logfile group 25; alter database clear logfile group 26; alter database clear logfile group 27; --删除log文件 ALTER DATABASE DROP STANDBY LOGFILE GROUP 14; ALTER DATABASE DROP STANDBY LOGFILE GROUP 15; ALTER DATABASE DROP STANDBY LOGFILE GROUP 16; ALTER DATABASE DROP STANDBY LOGFILE GROUP 17; ----------------------------传送密码文件、主库控制文件 --传送到备库需要修改密码文件的名字。备库的服务名(orapwzshdg) orapwd file=orapwzsh password=(sys用户的密码) force=y scp initzsh.ora orapwzsh 172.30.242.54:/oracle/dbhome/dbs --导出当前parameter文件 create pfile='/tmp/a' from spfile; ----------------------------主库parameter文件 *.audit_file_dest='/oracle/admin/zsh/adump' *.audit_trail='NONE' *.compatible='11.2.0.4.0' *.control_files='/oracle/oradata/zsh/zsh/control01.ctl','/oracle/fast_recovery_area/zsh/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='zsh' *.db_recovery_file_dest='/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=zshXDB)' *.fal_server='zshdg' *.log_archive_config='dg_config=(zsh,zshdg)' *.log_archive_dest_1='location=/oracle/oradata/zsh/arch/ valid_for=(all_logfiles, all_roles) db_unique_name=zsh' *.log_archive_dest_2='service=zshdg valid_for=(online_logfiles,primary_role) db_unique_name=zshdg' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.open_cursors=300 *.pga_aggregate_target=367001600 *.processes=200 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1101004800 *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' ----------------------------修改主库配置文件之后更改配置 alter system set fal_server='zshdg'; alter system set log_archive_config='dg_config=(zsh,zshdg)' sid='*'; alter system set log_archive_dest_1='location=/data/oracle/oradata/zsh/arch/ valid_for=(all_logfiles, all_roles) db_unique_name=zsh'; alter system set log_archive_dest_2='service=zshdg valid_for=(online_logfiles,primary_role) db_unique_name=zshdg' sid='*'; 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'; ----------------------------参数解释 -------主库设置dataguard开关zsh是主库的db_unique_name,zshdg是从库的db_unique_name alter system set log_archive_config='dg_config=(zsh,zshdg)' sid='*'; -------主库设置远程归档 alter system set log_archive_dest_3='service=zshdg valid_for=(online_logfiles,primary_role) db_unique_name=zshdg' sid='*'; ----------------------------主库检查(可在都配置结束之后再检查) set lines 500; set pages 500; -------一定要求确保 v$archive_dest 的 status 列为 ‘VALID’,遇到的问题dest2为disable 可能导致archive文件过不来 select status from v$archive_dest where destination is not null; -------如果返回结果 ‘APPLIED’的列 都是 ‘YES’ 或者只有最后一个是 'NO’的话,说明全部归档日志全部已经归档完了 select name,sequence#,archived,applied from v$archived_log order by sequence#; ----------------------------备库配置文件 *.audit_file_dest='/oracle/admin/zsh/adump' *.audit_trail='NONE' *.compatible='11.2.0.4.0' *.control_files='/oracle/oradata/zsh/zsh/control01.ctl','/oracle/fast_recovery_area/zsh/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='zsh' *.db_unique_name='zshdg' *.db_recovery_file_dest='/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=zshXDB)' *.fal_server='zsh' *.log_archive_config='dg_config=(zsh,zshdg)' *.log_archive_dest_1='location=/oracle/oradata/zsh/arch/ valid_for=(all_logfiles, all_roles) db_unique_name=zshdg' *.log_archive_dest_2='service=zsh valid_for=(online_logfiles,primary_role) db_unique_name=zsh' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.DB_FILE_NAME_CONVERT='/oracle/oradata/zsh/zsh/','/oracle/oradata/zsh/zsh/' *.LOG_FILE_NAME_CONVERT='/oracle/oradata/zsh/zsh/','/oracle/oradata/zsh/zsh/' *.open_cursors=300 *.pga_aggregate_target=367001600 *.processes=200 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1101004800 *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' ----------------------------创建主库parameter文件中所有的路径 mkdir -p /oracle/admin/zsh/adump mkdir -p /oracle/oradata/zsh/zsh/ mkdir -p /oracle/fast_recovery_area/zsh/ mkdir -p /oracle/oradata/zsh/arch/ mkdir -p /oracle/oradata/zsh/zsh/ ----------------------------启动从库实例,创建spfile文件,并启动在nomount状态 cd $ORACLE_HOME/dbs/ vi initzshdg.ora --把备库ora文件粘贴进去 startup pfile='/oracle/product/11.2.0.4/db_1/dbs/initzshdg.ora' nomount; ----------------------------设置listenr.ora文件 -------主库配置 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.10.102)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /oracle/product/11.2.0.4) (SID_NAME = zsh) (GLOBAL_DBNAME = zsh) ) ) -------从库配置 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.10.103)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (ORACLE_HOME=/oracle/product/11.2.0.4) (SID_NAME=zshdg) (GLOBAL_DBNAME=zshdg) ) ) ----------------------------设置tnsnames.ora文件 -------主库配置(文档中ZSH这个服务也不需要配置) ZSH = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.10.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zsh) ) ) ZSHDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.10.103)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zshdg) ) ) -------从库配置 ZSH = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.10.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zsh) ) ) ZSHDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.10.103)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zshdg) ) ) ----------------------------在主库测试连接 sqlplus sys/welcome@zshadg as sysdba ----------------------------在从库上恢复conctrolfile文件(把主库的control文件移动至备库主机) rman target / RMAN> restore controlfile from '/tmp/control.ctl'; --主库控制文件 ----------------------------在主库启动rman复制从库 rman target / auxiliary sys/welcome@zshadg RMAN> duplicate target database for standby from active database nofilenamecheck; ----------------------------从库打开应用日志 alter database mount SQL> alter database recover managed standby database disconnect from session; ----------------------------主库切换归档 SQL> alter system switch logfile; ----------------------------打开从库,关闭应用日志 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; 查看角色 select open_mode,PROTECTION_MODE, database_role, switchover_status from gv$database; 主库 select DEST_NAME,error from v$archive_dest where error is not null; 备库 select THREAD#,SEQUENCE#,applied,FIRST_TIME,NEXT_TIME from v$archived_log order by 2 ; 查看备库状态 set linesize 200 column name format a22 column value format a16 column unit format a28 column time_computed format a25 select * from v$dataguard_stats; 解决问题 一、ORA-16058 (1)在主库执行 set lines 500; set pages 500; select * from v$archive_dest where destination is not null; 出现 ORA-16058: standby database instance is not mounted报错 (2)在从库执行 手动把数据库到mount状态 alter database recover managed standby database using current logfile disconnect from session;