DATA guard 搭建内容 主库通过网络把日志文件传输到副库 实现原理：主库是好的，副库安装数据库软件 oracle软件目录 /u01/app/oraI 查看环境变量 env|grep ORACLE ps -ef|grep smon v$database 反应的是控制文件信息 搭建DG必须开归档模式 开启快速恢复区： 默认路径db_recovery_file_dest /u01/app/oracle/fast_recovery_area 大小 db_recovery_file_dest_size 4977m 如果没开启，先设置大小，在设置路径 log_archive_dest_数字 配置归档目的地 最大31个 oracle默认能在这31个文件设置归档 如果没配置归档目的地，oracle将归档日志放在快速恢复区中 查看是否开启归档是否使用快速恢复区 archive log list 检索视图 select log_mode,force_logging from v$database; alter database force logging; define _editor=vim edit liushuwei.sql ---简写ed liushuwei oerr ora 17628 select SEQUENCE#,applied from v$archived_log; SELECT * FROM V$MANAGED_STANDBY DG三种保护模式： 最高可用性，最大保护，最高性能（企业常用） 备库分为，物理和逻辑 物理备库：recover技术 mount状态（可以得到完成主库的备份）11g特性 ADG（活动的DG）---由mount状态变为readonly状态 实现主备的读写分离 逻辑备库：logminer技术（数据挖掘）主库的备份转回为sql语句 open状态
DATA 搭建步骤
1.主库操作 1.1配置强制日志模式和归档： [oracle@edbjr2p1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 30 17:52:58 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@PROD1> select log_mode,force_logging from v$database; LOG_MODE FOR ------------ --- ARCHIVELOG NO SYS@PROD1> alter database force logging; Database altered. SYS@PROD1> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 29 Next log sequence to archive 31 Current log sequence 31 SYS@PROD1> show parameter db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /home/oracle/flash db_recovery_file_dest_size big integer 4G SYS@PROD1> alter system switch logfile; System altered. 1.2 创建Standby logfile： SYS@PROD1> ed1 alter database add standby logfile group 4 ('/u01/app/oracle/oradata/PROD1/stlog01.log') size 100m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/PROD1/stlog02.log') size 100m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/PROD1/stlog03.log') size 100m; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/PROD1/stlog04.log') size 100m; SYS@PROD1> @1 Database altered. Database altered. Database altered. Database altered. 1.3 创建参数文件： SYS@PROD1> create pfile from spfile; File created. 1.4 修改参数文件： [oracle@edbjr2p1 ~]$ cd $ORACLE_HOME/dbs [oracle@edbjr2p1 dbs]$ vi initPROD1.ora DB_NAME=PROD1 DB_UNIQUE_NAME=PROD1 LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB1)' LOG_ARCHIVE_DEST_1= 'LOCATION=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD1' LOG_ARCHIVE_DEST_2= 'SERVICE=SBDB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB1' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=SBDB1 DB_FILE_NAME_CONVERT='SBDB1','PROD1' LOG_FILE_NAME_CONVERT='SBDB1','PROD1' STANDBY_FILE_MANAGEMENT=AUTO dg_broker_start=true 1.5 修改监听文件： [oracle@edbjr2p1 dbs]$ vi ../network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=PROD1.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=PROD1)) (SID_DESC= (GLOBAL_DBNAME=PROD1_DGMGRL.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=PROD1)) (SID_DESC= (SID_NAME=plsextproc) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (PROGRAM=extproc))) [oracle@edbjr2p1 dbs]$ vi ../network/admin/tnsnames.ora ##添加上如下内容 SBDB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SBDB1.us.oracle.com) ) ) 1.6 传输文件到备库主机： [oracle@edbjr2p1 dbs]$ scp initPROD1.ora edbjr2p2:/u01/app/oracle/product/11.2.0/db_1/dbs/initSBDB1.ora oracle@edbjr2p2's password: initPROD1.ora 100% 1385 1.4KB/s 00:00 [oracle@edbjr2p1 dbs]$ scp orapwPROD1 edbjr2p2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB1 oracle@edbjr2p2's password: orapwPROD1 100% 1536 1.5KB/s 00:00 [oracle@edbjr2p1 dbs]$ scp ../network/admin/listener.ora ../network/admin/tnsnames.ora edbjr2p2:/u01/app/oracle/product/11.2.0/db_1/network/admin oracle@edbjr2p2's password: listener.ora 100% 837 0.8KB/s 00:00 tnsnames.ora 100% 1971 1.9KB/s 00:00 1.7 重启主库使配置生效： [oracle@edbjr2p1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 30 18:06:13 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@PROD1> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@PROD1> create spfile from pfile; File created. SYS@PROD1> startup ORACLE instance started. Total System Global Area 941600768 bytes Fixed Size 1348860 bytes Variable Size 553650948 bytes Database Buffers 381681664 bytes Redo Buffers 4919296 bytes Database mounted. Database opened. SYS@PROD1> 2.备库操作 2.1 创建对应路径： [oracle@edbjr2p2 ~]$ cd /u01/app/oracle/ [oracle@edbjr2p2 oracle]$ mkdir -p admin/SBDB1/adump [oracle@edbjr2p2 oracle]$ mkdir -p oradata/SBDB1 [oracle@edbjr2p2 dbs]$ mkdir -p /home/oracle/flash 2.2 修改参数文件： [oracle@edbjr2p2 oracle]$ cd $ORACLE_HOME/dbs [oracle@edbjr2p2 dbs]$ vi initSBDB1.ora #追加到 DB_NAME=PROD1 DB_UNIQUE_NAME=SBDB1 # 区分主备 LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB1,PROD1)' # 数据库名，主|备 LOG_ARCHIVE_DEST_1= # 本地归档 'LOCATION=use_db_recovery_file_dest # 需为本地目录 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) # 日志与角色选择（all,standby,online; zhu,bei,all) DB_UNIQUE_NAME=SBDB1' # 应用于此数据库 LOG_ARCHIVE_DEST_2= # 远程归档 'SERVICE=PROD1 ASYNC # tnsname.ora 的别名 / 传输模式：ASYNC 异步 | SYNC 同步 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) # 日志与角色 DB_UNIQUE_NAME=PROD1' # LOG_ARCHIVE_DEST_STATE_1=ENABLE # 表示归档目的地可用 LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE # 远程登陆 LOG_ARCHIVE_FORMAT=%t_%s_%r.arc # 归档日志格式 使用快速恢复区时（自己管理），此参数无效。 FAL_SERVER=PROD1 # 获取归档日志的服务器 备库使用参数1 DB_FILE_NAME_CONVERT='PROD1','SBDB1' # 日志名转换时 后面的替换前面的，可有多项 1替2，3替4等; 2 LOG_FILE_NAME_CONVERT='PROD1','SBDB1' # 3 STANDBY_FILE_MANAGEMENT=AUTO # 4 dg_broker_start=true 2.3 修改网络配置文件： [oracle@edbjr2p2 dbs]$ vi ../network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=SBDB1.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=SBDB1)) (SID_DESC= (GLOBAL_DBNAME=SBDB1_DGMGRL.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=SBDB1)) (SID_DESC= (SID_NAME=plsextproc) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (PROGRAM=extproc))) 2.4 重启网络配置： [oracle@edbjr2p2 dbs]$ lsnrctl reload LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-APR-2017 18:14:15 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=edbjr2p2.example.com)(PORT=1521))) The command completed successfully [oracle@edbjr2p2 dbs]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 30-APR-2017 18:14:22 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=edbjr2p2.example.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 30-APR-2017 17:22:08 Uptime 0 days 0 hr. 52 min. 14 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/edbjr2p2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr2p2.example.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "EMREP.us.oracle.com" has 1 instance(s). Instance "EMREP", status READY, has 1 handler(s) for this service... Service "EMREPXDB.us.oracle.com" has 1 instance(s). Instance "EMREP", status READY, has 1 handler(s) for this service... Service "ORCLXDB" has 1 instance(s). Instance "PROD3", status READY, has 1 handler(s) for this service... Service "PROD3" has 1 instance(s). Instance "PROD3", status READY, has 1 handler(s) for this service... Service "PROD4.us.oracle.com" has 1 instance(s). Instance "PROD4", status READY, has 1 handler(s) for this service... Service "PROD4XDB.us.oracle.com" has 1 instance(s). Instance "PROD4", status READY, has 1 handler(s) for this service... Service "SBDB1.us.oracle.com" has 1 instance(s). Instance "SBDB1", status UNKNOWN, has 1 handler(s) for this service... Service "SBDB1_DGMGRL.us.oracle.com" has 1 instance(s). Instance "SBDB1", status UNKNOWN, has 1 handler(s) for this service... Service "plsextproc" has 1 instance(s). Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@edbjr2p2 dbs]$ sudo mount -o remount,size=4g /dev/shm 2.5 启动辅助实例： $ cd /u01/app/oracle $ mkdir fast_recovery_area; $ cd fast_recovery_area; $ mkdir sbdb1; [oracle@edbjr2p2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 30 18:16:40 2017 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 941600768 bytes Fixed Size 1348860 bytes Variable Size 553650948 bytes Database Buffers 381681664 bytes Redo Buffers 4919296 bytes SQL> 3.搭建备库： 3.1 使用复制数据库技术搭建备库： [oracle@edbjr2p1 ~]$ rman target / auxiliary sys/oracle@sbdb1 # 主库操作，主库@备库 Recovery Manager: Release 11.2.0.3.0 - Production on Sun Apr 30 18:17:30 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD1 (DBID=2082231315) connected to auxiliary database: PROD1 (not mounted) # 批处理命令，连续操作需清空之前数据 RMAN> DUPLICATE TARGET DATABASE # 复制目标库 FOR STANDBY # standby 表复制为备库 FROM ACTIVE DATABASE # DORECOVER # 应用归档 NOFILENAMECHECK; # 文件名检查，同台机器需检查，不同的，异地不检查 3.2 使用broker管理DG： [oracle@edbjr2p1 ~]$ dgmgrl / DGMGRL for Linux: Version 1 1.2.0.3.0 - Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> help create Creates a broker configuration Syntax: CREATE CONFIGURATION <configuration name> AS PRIMARY DATABASE IS <database name> CONNECT IDENTIFIER IS <connect identifier>; ##创建配置 DGMGRL> CREATE CONFIGURATION dgbroker as PRIMARY DATABASE IS prod1 CONNECT IDENTIFIER IS prod1; Configuration "dgbroker" created with primary database "prod1" DGMGRL> help add Adds a standby database to the broker configuration Syntax: ADD DATABASE <database name> [AS CONNECT IDENTIFIER IS <connect identifier>] [MAINTAINED AS {PHYSICAL|LOGICAL}]; ##添加备库到配置 DGMGRL> ADD DATABASE sbdb1 AS CONNECT IDENTIFIER IS sbdb1 MAINTAINED AS PHYSICAL; Database "sbdb1" added DGMGRL> show configuration; Configuration - dgbroker Protection Mode: MaxPerformance Databases: prod1 - Primary database sbdb1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED ##启用配置 DGMGRL> enable configuration; Enabled. DGMGRL> show configuration; Configuration - dgbroker Protection Mode: MaxPerformance Databases: prod1 - Primary database sbdb1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> 备注：所用命令整理 select log_mode,force_logging from v$database; alter database force logging; archive log list; show parameter db_recovery alter system switch logfile; alter database add standby logfile group 4 ('/u01/app/oracle/oradata/PROD1/stlog01.log') size 100m; alter database add standby logfile group 5 ('/u01/app/oracle/oradata/PROD1/stlog02.log') size 100m; alter database add standby logfile group 6 ('/u01/app/oracle/oradata/PROD1/stlog03.log') size 100m; alter database add standby logfile group 7 ('/u01/app/oracle/oradata/PROD1/stlog04.log') size 100m; create pfile from spfile; $ vi initPROD1.ora DB_NAME=PROD1 DB_UNIQUE_NAME=PROD1 LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB1)' LOG_ARCHIVE_DEST_1= 'LOCATION=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD1' LOG_ARCHIVE_DEST_2= 'SERVICE=SBDB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB1' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=SBDB1 DB_FILE_NAME_CONVERT='SBDB1','PROD1' LOG_FILE_NAME_CONVERT='SBDB1','PROD1' STANDBY_FILE_MANAGEMENT=AUTO STANDBY_FILE_MANAGEMENT=AUTO dg_broker_start=true $ vi ../network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p1.example.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=PROD1.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=PROD1)) (SID_DESC= (GLOBAL_DBNAME=PROD1_DGMGRL.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=PROD1)) (SID_DESC= (SID_NAME=plsextproc) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (PROGRAM=extproc))) $ vi ../network/admin/tnsnames.ora ##添加上如下内容 SBDB1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = SBDB1.us.oracle.com) ) ) scp initPROD1.ora edbjr2p2:/u01/app/oracle/product/11.2.0/db_1/dbs/initSBDB1.ora scp orapwPROD1 edbjr2p2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB1 #口令文件备库不可重建，需用主库的 scp ../network/admin/listener.ora ../network/admin/tnsnames.ora edbjr2p2:/u01/app/oracle/product/11.2.0/db_1/network/admin create spfile from pfile; startup cd /u01/app/oracle/ mkdir -p admin/SBDB1/adump mkdir -p oradata/SBDB1 mkdir -p /home/oracle/flash $ cd $ORACLE_HOME/dbs $ vi initSBDB1.ora DB_NAME=PROD1 DB_UNIQUE_NAME=SBDB1 LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB1,PROD1)' LOG_ARCHIVE_DEST_1= 'LOCATION=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SBDB1' LOG_ARCHIVE_DEST_2= 'SERVICE=PROD1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD1' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=PROD1 DB_FILE_NAME_CONVERT='PROD1','SBDB1' LOG_FILE_NAME_CONVERT='PROD1','SBDB1' STANDBY_FILE_MANAGEMENT=AUTO STANDBY_FILE_MANAGEMENT=AUTO dg_broker_start=true $ vi ../network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = edbjr2p2.example.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=SBDB1.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=SBDB1)) (SID_DESC= (GLOBAL_DBNAME=SBDB1_DGMGRL.us.oracle.com) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=SBDB1)) (SID_DESC= (SID_NAME=plsextproc) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (PROGRAM=extproc))) lsnrctl reload $ sudo mount -o remount,size=4g /dev/shm $ rman target / auxiliary sys/oracle@sbdb1 DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK; $ dgmgrl sys/oracle@prod1 help create CREATE CONFIGURATION dgbroker as PRIMARY DATABASE IS prod1 CONNECT IDENTIFIER IS prod1; ADD DATABASE sbdb1 AS CONNECT IDENTIFIER IS sbdb1 MAINTAINED AS PHYSICAL; show configuration; enable configuration; ##可能用到的命令 create configuration dgbroker as primary database is 'PROD1' connect identifier is 'PROD1'; add database 'SBDB1' as connect identifier is 'SBDB1' maintained as physical; convert database SBDB1 to SNAPSHOT STANDBY; alter database recover managed standby database cancel; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
