oracle dataguard 搭建手册

发布时间：2020-12-12 15:06:44 所属栏目：百科来源：网络整理

导读：oracle dataguard 搭建手册一、准备工作 1、安装数据库软件：主库和备库安装数据库软件：步骤7：选择数据库的恢复区：勾选指定快速恢复选项和启用归档。 2、参数开启归档功能 sqlplus /nolog conn /as sysdba 查看快速回复区参数 show parameter db_recov

oracle dataguard 搭建手册一、准备工作 1、安装数据库软件：主库和备库安装数据库软件：步骤7：选择数据库的恢复区：勾选指定快速恢复选项和启用归档。 2、参数开启归档功能 sqlplus /nolog conn /as sysdba 查看快速回复区参数 show parameter db_recovery 设置快速回复区参数 alter system set db_recovery_file_dest_size=4182M scope=spfile; alter system set db_recovery_file_dest='D:DATAREOV' scope=spfile; 检查日志模式 archive loglist; 开启归档模式 shutdown immediate; startup mount; alter database archivelog; alter database open; 查看快速恢复区使用空间 select name,SPACE_LIMIT,SPACE_USED from v$recovery_file_dest; 修改归档日志路径，快速恢复区不要和归档处于同一目录下 alter system set log_archive_dest_1='location=d:logorcl'; --开启之后检查归档目录是否生成文件。二、数据库配置 1、不同数据库名，不同SID。主库： IP:192.168.199.177 数据库名:orcl 数据库SID:orcl DB_UNIQUE_NAME:orcl 数据库安装路径：D:oracleA 数据文件路径：D:DATABASE 本地归档路径：D:DATAREOV listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL) (ORACLE_HOME = D:oracleAproduct11.2.0dbhome_1) (SID_NAME = ORCL) ) ) tnsname.ora ORCLBAK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLBAK) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) ) spfile.ora: *.db_unique_name=orcl *.log_archive_config='dg_config=(orcl,orclbak)' *.log_archive_dest_1='location=D:DATAREOVorcl valid_for=(all_logfiles,all_roles) db_unique_name=ORCL' *.log_archive_dest_2='service=ORCLBAK async valid_for=(online_logfiles,primary_role) db_unique_name=ORCLBAK' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.fal_server=orclbak *.fal_client=orcl *.standby_file_management=auto 备库： IP:192.168.199.179 数据库名:orclbak 数据库SID:orclbak DB_UNIQUE_NAME:orclbak 数据库安装路径：D:oracle 数据文件路径：D:DATABASE 本地归档路径：D:DATAREOV listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCLBAK) (ORACLE_HOME = D:oracleproduct11.2.0dbhome_1) (SID_NAME = ORCLBAK) ) ) tnsname.ora ORCLBAK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLBAK) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) ) spfile.ora: *.db_unique_name=orclbak *.log_archive_config='dg_config=(orcl,orclbak)' *.log_archive_dest_1='location=D:DATAREOVorclbak valid_for=(all_logfiles,all_roles) db_unique_name=ORCLBAK' *.log_archive_dest_2='service=ORCL async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.fal_server=orcl *.fal_client=orclbak *.standby_file_management=auto 2、同数据库名，同SID。主库： IP:192.168.199.177 数据库名:orcl 数据库SID:orcl DB_UNIQUE_NAME:orcl 数据库安装路径：D:oracleA 数据文件路径：D:DATABASE 本地归档路径：D:DATAREOV listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL) (ORACLE_HOME = D:oracleAproduct11.2.0dbhome_1) (SID_NAME = ORCL) ) ) tnsname.ora ORCLBAK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) spfile.ora: *.db_unique_name=orcl *.log_archive_config='dg_config=(orcl,all_roles) db_unique_name=orcl' *.log_archive_dest_2='service=ORCLBAK async valid_for=(online_logfiles,primary_role) db_unique_name=orclbak' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.fal_server=orclbak *.fal_client=orcl *.standby_file_management=auto 备库： IP:192.168.199.179 数据库名:orcl 数据库SID:orcl DB_UNIQUE_NAME:orclbak 数据库安装路径：D:oracle 数据文件路径：D:DATABASE 本地归档路径：D:DATAREOV listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = D:oracleproduct11.2.0dbhome_1) (SID_NAME = orcl) ) ) tnsname.ora ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) ) ) ORCLBAK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) spfile.ora: *.db_unique_name=orclbak *.log_archive_config='dg_config=(orcl,orclbak)' *.log_archive_dest_1='location=D:logorcl valid_for=(all_logfiles,all_roles) db_unique_name=orclbak' *.log_archive_dest_2='service=ORCL async valid_for=(online_logfiles,primary_role) db_unique_name=orcl' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.fal_server=orcl *.fal_client=orclbak *.standby_file_management=auto 二、主库处理： 1.调整数据库模式 --检查数据库模式 select force_logging from v$database; --修改模式 alter database force logging; 2.设置standby日志文件 --增加standby redo log select group#,member from v$logfile; --检查日志文件大小 select group#,bytes/(1024*1024) from v$log; --增加日志组 alter database add standby logfile group 4 'D:DATABASEORCLorclbak_redo04.log' size 50m; alter database add standby logfile group 5 'D:DATABASEORCLorclbak_redo05.log' size 50m; alter database add standby logfile group 6 'D:DATABASEORCLorclbak_redo06.log' size 50m; alter database add standby logfile group 7 'D:DATABASEORCLorclbak_redo07.log' size 50m; --检查创建结果 select group#,member from v$logfile where type='STANDBY'; 3.修改主库pfile文件 --创建pfile文件 create pfile='d:1.ora' from spfile; --在1.ora中增加,并保存。按照配置选择spfile.ora参数添加 4.复制密码文件到备库相同路径并修改为备库文件名。 D:oracleAproduct11.2.0dbhome_1databaseINITorcl.ORA 5.创建备库的控制文件，要求数据库是OPEN状态。 --检查控制文件 select * from v$controlfile; --创建备库使用的控制文件，将CONTROL01.CTL复制CONTROL02.CTL和CONTROL03.CTL一起拷贝到备库去。 alter database create standby controlfile as 'd:CONTROL01.CTL'; 6.关闭数据库，拷贝文件到备库。 shutdown immdediate; 二、备库处理： 1.根据主库数据库摸板建立备库数据库。 2.拷贝主库*.dbf、redo*.log、std*.log和密码文件到备库相应文件位置，并覆盖原有文件（原有文件可做好备份）。 3.修改备库pfile文件 --创建pfile文件 create pfile='d:1.ora' from spfile; --在1.ora中增加,并保存。按照配置选择spfile.ora参数添加 4.启动备库 --启动到nomount状态 startup pfile='D:1.ora' nomount; --启动到mount状态 alter database mount orclbak database; --开启备库监听 alter database recover managed standby database disconnect from session; 三、启动主备库 1.启动顺序：先启动备库，再启动主库。关闭顺序：先关主库，再关备库。 1.启动备库不可读，可同步。 --主库启动,可以更新spfile中的参数，之后默认使用新参数。 startup pfile='D:1.ora'; --备库启动，此启动方式备库不能以只读方式访问。 startup pfile='D:1.ora' nomount; alter database mount standby database; --备库检查RFS程序是否启动 select process,pid,status,client_process from v$managed_standby; --备库启动REDO APPLY alter database recover managed standby database disconnect from session; --备库检查程序启动情况，PROCESS:MRP0,STATUS：WAIT_FOR_LOG,CLIENT_P:N/A。 select process,client_process from v$managed_standby; 2.启动备库可读，可同步。 --主库启动,可以更新spfile中的参数，之后默认使用新参数。 startup pfile='D:1.ora'; --备库启动，启动后以只读方式访问 startup pfile='D:1.ora' nomount; alter database mount standby database; alter database open read only; select process,client_process from v$managed_standby; alter database recover managed standby database disconnect from session; select process,client_process from v$managed_standby; --检查数据库的状态,READ ONLY WITH APPLY 只读模式打开可以应用重做日志，同步会需要一定时间。只有处于READ ONLY WITH APPLY和MOUNTED状态才能同步。 select open_mode,database_role from v$database; 四、切换主备库 1、检查主库的切换状态。PRIMARY TO STANDBY没有用户连接可以切换，SESSIONS ACTIVE select database_role,switchover_status from v$database; 2、在当前主库上初始化切换到备库，备库会自动接收转为主库 alter database commit to switchover to physical standby with session shutdown wait; 3、将切换后成为主库执行语句不执行外部操作。 shutdown immediate; startup nomunt; 4、检查主库和备库的切换状态 select database_role,switchover_status from v$database; 5、在新的备库启动日志应用 alter database recover managed standby database disconnect from session; 6、检查程序启动情况，PROCESS:MRP0,client_process from v$managed_standby; 7、检查备库的切换状态 select database_role,switchover_status from v$database; 8、切换备库到主库 alter database commit to switchover to primary with session shutdown wait; 9、打开主库 alter database open; 10、检查主库的切换状态 select database_role,switchover_status from v$database; 11、进行检测日志等是否切换成功。五、相关命令 --查看pfile启动路径 show parameter spfile show parameter pfile --使用pfile文件更新spfile中的参数,请备份好原有pfile文件 create spfile from pfile='D:1.ora'; --手动切换日志,触发提交日志到备库 alter system switch logfile; --检查主备库查询当前日志序列号 select sequence# from v$log; --查看已经归档日志情况 select name,sequence#,thread# from v$archived_log; --检查表空间 select name from v$datafile; --检查日志状态 archive log list; --检查主库备库切换状态 --主库：PRIMARY TO STANDBY --备库：PHYSICAL STANDBY NOT ALLOWED select database_role,switchover_status from v$database; --检查数据库的状态,database_role from v$database; --启动备库应用日志传送模式 alter database recover managed standby database disconnect from session; --启动备库应用日志传送模式，并使备库立即应用归档日志 alter database recover managed standby database using current logfile disconnect from session; 相关语句： --取消备库监听 alter database recover managed standby database cancel; --查看数据库保护模式 select protection_mode from v$database; --更改保护模式 alter database set standby database to maximize protection; alter database set standby database to maximize availability; alter database set standby database to maximize performancen;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!