data guard 的部署

来源：这里教程网时间：2026-03-03 13:53:58 作者：

tnsname.ora fal_client fal_server data guard 的部署 ************************************************************ 创建物理standby：一、准备工作： 1、确保数据库处于归档模式 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Current log sequence 5 SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 4122M recovery_parallelism integer 0 SQL> alter system set db_recovery_file_dest_size=1G; System altered. SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 1G recovery_parallelism integer 0 SQL> SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 722366464 bytes Fixed Size 2231872 bytes Variable Size 436208064 bytes Database Buffers 281018368 bytes Redo Buffers 2908160 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. 2、并打开 Forced Logging 模式 SQL> select force_logging from v$database; FOR --- NO SQL> alter database force logging; Database altered. 2、创建密码文件(保持两个节点存在，否则集群切换到另一节点就会找不到密码文件) orapwd file=$ORACLE_HOME/dbs/orapworc1 password=welcome1 force=y ignorecase=y 3、更改数据库的name（db_unique_name） SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string orcl lock_name_space string log_file_name_convert string processor_group_name string service_names string orcl SQL> alter system set db_unique_name=orcl scope=spfile; System altered. 4、更改standby_file_management SQL> show parameter standby NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_archive_dest string ?/dbs/arch standby_file_management string MANUAL SQL> alter system set standby_file_management=AUTO; System altered. SQL> show parameter standby NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_archive_dest string ?/dbs/arch standby_file_management string AUTO 5、修改fal服务器端和客户端配置 SQL> alter system set fal_client=orcl; System altered. SQL> alter system set fal_server=orcldg; System altered. SQL> show parameter fal; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string ORCL fal_server string ORCLDG 6、修改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=(orcl,orcldg)'; System altered. SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(orcl,orcldg) 7、修改log_archive_dest_2参数 SQL> alter system set log_archive_dest_state_2='defer'; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SQL> alter system set log_archive_dest_2='service=orcldg lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcldg'; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service=orcldg lgwr sync affir m valid_for=(online_logfiles,p rimary_role) db_unique_name=or cldg SQL> 9，进入rman进行备份查看数据库的大小 select sum(bytes)/1024/1024/1024 from v$datafile; 备份控制文件 run { backup database format '/home/oracle/rman_backup/FULL_%U.bak'; backup archivelog all format '/home/oracle/rman_backup/ARC_%U.bak'; backup format '/home/oracle/rman_backup/standby_%U.ctl' current controlfile for standby; } backup device type disk format '/rman_backup/standby_%U.ctl' current controlfile for standby; 恢复控制文件的方法 RMAN> restore standby controlfile from '/home/oracle/rman_backup/standby_0tpk53jq_1_1.ctl'; -----路径为控制文件备份片的路径 10，创建orcldg的pfile SQL> create pfile='/home/oracle/orcldgpfile.ora' from spfile; File created. 11、传输orcldg的pfile [oracle@oracle1 ~]$ scp orcldgpfile.ora oracle@192.168.74.21:/home/oracle oracle@192.168.74.21's password: orcldgpfile.ora 12、修改参数文件（备库） [oracle@oracle ~]$ vim orcldgpfile.ora [oracle@oracle ~]$ cat orcldgpfile.ora *.audit_file_dest='/u01/app/oracle/admin/orcldg/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl','/u01/app/oracle/fast_recovery_area/orcldg/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=1073741824 *.db_unique_name='ORCLDG' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.fal_client='ORCLDG' *.fal_server='ORCL' *.log_archive_config='dg_config=(orcldg,orcl)' *.log_archive_dest_2='service=orcl lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl' *.memory_target=725614592 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@oracle ~]$ mkdir -p /u01/app/oracle/admin/orcldg/adump [oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg/ [oracle@oracle ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcldg/ 13、修改注册文件 [oracle@oracle ~]$ cat /etc/oratab orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N orcldg:/u01/app/oracle/product/11.2.0/dbhome_1:N 14、尝试启动数据库到nomount状态并创建spfile从pfile中 [oracle@oracle ~]$ . oraenv ORACLE_SID = [oracle] ? orcldg The Oracle base remains unchanged with value /u01/app/oracle [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 3 16:56:59 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/orcldgpfile.ora'; ORACLE instance started. Total System Global Area 722366464 bytes Fixed Size 2231872 bytes Variable Size 432013760 bytes Database Buffers 285212672 bytes Redo Buffers 2908160 bytes SQL> create spfile from pfile='/home/oracle/orcldgpfile.ora'; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> 15、启动数据库到nomount SQL> startup nomount; ORACLE instance started. Total System Global Area 722366464 bytes Fixed Size 2231872 bytes Variable Size 432013760 bytes Database Buffers 285212672 bytes Redo Buffers 2908160 bytes 16、修改convert参数 SQL> show parameter convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string log_file_name_convert string SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile; System altered. SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcldg' scope=spfile; System altered. 重启实例convert 参数生效 SQL> show parameter convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string log_file_name_convert string SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 722366464 bytes Fixed Size 2231872 bytes Variable Size 432013760 bytes Database Buffers 285212672 bytes Redo Buffers 2908160 bytes SQL> show parameter convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /u01/app/oracle/oradata/orcl, /u01/app/oracle/oradata/orcldg log_file_name_convert string /u01/app/oracle/oradata/orcl, /u01/app/oracle/oradata/orcldg [oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg [oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/orcldg 18.配置监听服务名 [oracle@oracle1 ~]$ tnsping orcldg-192.168.74.21 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 04-MAR-2017 17:37:00 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcldg))) OK (10 msec) [oracle@oracle1 ~]$ [oracle@oracle ~]$ tnsping orcl-192.168.74.22 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 17:36:51 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.22)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (0 msec) 配置静态监听 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcldg) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcldg) ) (SID_DESC = (GLOBAL_DBNAME = Oracle8) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = ORCL) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ) ADR_BASE_LISTENER = /u01/app/oracle 配置服务名（）名称相同 [oracle@oracle admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.22)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCLDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcldg) ) ) [oracle@oracle1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.22)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCLDG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.74.21)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcldg) ) ) [oracle@oracle admin]$ vim listener.ora [oracle@oracle admin]$ lsnrctl reload LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 18:42:36 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))) The command completed successfully [oracle@oracle admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 18:42:45 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 03-MAR-2017 18:20:25 Uptime 0 days 0 hr. 22 min. 19 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521))) Services Summary... Service "Oracle8" has 1 instance(s). Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service... Service "orcldg" has 2 instance(s). Instance "orcldg", status UNKNOWN, has 1 handler(s) for this service... Instance "orcldg", status BLOCKED, has 1 handler(s) for this service... The command completed successfully [oracle@oracle admin]$ rman target sys/oracle@orcl-192.168.74.22 auxiliary sys/oracle@orcldg nocatalog; Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 3 18:42:56 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1464936028) using target database control file instead of recovery catalog connected to auxiliary database: ORCL (not mounted) RMAN> duplicate target database for standby from active database; duplicate target database for standby from active database nofilenamecheck; restore standby controlfile from '/home/oracle/rman_backup/standby_05u0o7s4_1_1.ctl' restore database; 配置redologfile SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcldg MOUNTED select member from v$logfile; SQL> select bytes/1024/1024 from v$log; SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcldg/redo04.log') size 50M; Database altered. SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcldg/redo05.log') size 50M; Database altered. SQL> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcldg/redo06.log') size 50M; Database altered. SQL> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcldg/redo07.log') size 50M; Database altered. alter database add standby logfile group 4 size 50M ,group 5 size 50M ,group 6 size 50M, group 7 size 50M; alter database add standby logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M; 开启日志应用进程： SQL> alter database recover managed standby database parallel 2 using current logfile disconnect; Database altered. 开启同步开关配置 SQL> alter system set log_archive_dest_state_2='enable'; System altered. 查看数据库的主角色（保护级别，模式等） SQL> DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ---------------- -------------------- -------------------- -------------------- PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 查看日志应用 SQL> select max(sequence#),applied from v$archived_log group by applied; MAX(SEQUENCE#) APPLIED -------------- --------- 18 YES SQL> select max(sequence#),applied from v$archived_log group by applied; MAX(SEQUENCE#) APPLIED -------------- --------- 18 YES SQL> / MAX(SEQUENCE#) APPLIED -------------- --------- 19 IN-MEMORY 18 YES SQL> / MAX(SEQUENCE#) APPLIED -------------- --------- 19 YES 打开数据库 SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open; Database altered. SQL> SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> select max(sequence#),applied from v$archived_log group by applied; MAX(SEQUENCE#) APPLIED -------------- --------- 20 YES SQL> / MAX(SEQUENCE#) APPLIED -------------- --------- 21 YES SQL> / MAX(SEQUENCE#) APPLIED -------------- --------- 21 YES SQL> select * from t1; 查看数据库的状态 SQL> select name,open_mode,protection_level,database_role,switchover#,switchover_status from v$database; NAME OPEN_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER# SWITCHOVER_STATUS --------- -------------------- -------------------- ---------------- ----------- -------------------- ORCL READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY 1464842362 NOT ALLOWED 以上active dataguard部署完毕 ###################################################################################################################################################################### logcal 数据库（逻辑standby） 3.配置主备监听(必须保证双向监听正常，并且配置RAC集群两个节点) 4、设置初始化参数（primary和standby）主库参数 *.log_archive_config='dg_config=(kc,dg)' *.log_archive_dest_2='service=dg db_unique_name=dg lgwr async' *.log_archive_dest_state_2='ENABLE' 备库参数(config中应该是 net service name,而不是sid或service name) *.db_unique_name='dg' *.undo_management='AUTO' *.log_archive_config='dg_config=(kc,dg)' *.db_file_name_convert='+DATA01/kc/datafile','+DATA01/dg/datafile' *.log_file_name_convert='+DATA01/kc/onlinelog','DATA01/dg/onlinelog' *.standby_file_management=auto *.fal_client='kc' *.fal_server='kc' 二、详细步骤： 1、主库创建备份(手工复制数据文件或通过 RMAN) 2、主库创建控制文件通过下列语句为 standby 数据库创建控制文件 SQL> alter database create standbycontrolfile as 'd:\backup\jsspdg01.ctl'; 3、主库创建并修改初始化参数文件 SQL> create pfile='d:\backup\initjsspdg.ora' from spfile; 注意 primary 和 standby 不同角色的属性配置，注意文件路径。 4、复制文件到 standby 服务器数据文件，控制文件，修改过的初始化参数文件，注意路径。 5、配置 standby 数据库 6、启动 standby SQL> STARTUP MOUNT; 需要针对每个thread创建3个standby日志组 SQL> alter database add standby logfile thread 1 group 5 ('+DATA01/dg/onlinelog/st_1_5.log')size 50M, group 6 ('+DATA01/dg/onlinelog/st_1_6.log')size 50M, group 7 ('+DATA01/dg/onlinelog/st_1_7.log')size 50M; SQL> alter database add standby logfile thread 2 group 8 ('+DATA01/dg/onlinelog/st_2_8.log')size 50M, group 9 ('+DATA01/dg/onlinelog/st_2_9.log')size 50M, group 10 ('+DATA01/dg/onlinelog/st_2_10.log')size 50M; 启动 redo 应用 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 启动实时应用 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 7、停止 standby 正常情况下，我们停止也应该是先停止 redo 应用，可以通过下列语句： SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 然后再停止 standby 数据库 SQL> SHUTDOWN IMMEDIATE; 8.常用状态查询： ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; select process,status from v$managed_standby; show parameter instance_name; select max(sequence#) from v$archived_log; 9.准备切换： select switchover_status from v$database; alter database commit to switchover to physical standby;

编辑推荐：

返回列表

data guard 的部署

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推