Oracle 11g DataGuard 配置详细说明
1．判断DataGuard是否安装
select * from v$option where parameter ='Oracle Data Guard';
2. 数据库环境说明
主库配置：IP：192.168.228.133(Oracle11g1)，数据库名：db1,监听服务名：pri
从库配置：IP：192.168.229.134(Oracle11g2)，数据库名：db1，监听服务名：dg
数据库程序安装路径：/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs
数据库存放路径：/oracledata/db1
3.监听配置
在做oracle dataguard主从库配置时候，一定要配置静态监听，否则可能出现监听服务解析错误，不能连接的问题，监听配置如下：
主库 配置如下：
[oracle@Oracle11g1 admin]$ pwd
/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@Oracle11g1 admin]$ cat listener.ora
# listener.ora Network Configuration File:/oracleapp/oinstall/oracle/product/1.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
WU =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g2)(PORT = 1521))
)
)
SID_LIST_WU =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pri)
(ORACLE_HOME =/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1)
(SID_NAME = db1)
)
)
ADR_BASE_WU = /oracleapp/oinstall/oracle
#注意这里的global_dbname=pri,SID_NAME=db1,这个SID_NAME 应与你对外提供服务的 $ORACLE_SID 一致,即数据库：db1
[oracle@Oracle11g1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
)
)
dg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg)
)
)
#tnsname pri 中的 SERVICE_NAME=pri,这里的服务名为 pri而不是通常的 db1，因为在 listener.ora 中已经注册了 pri,lsnrctl 启动时会监听 pri ，并对应到 SID_NAME=db1 上。
从库配置如下：
[oracle@Oracle11g2 admin]$ cat listener.ora
# listener.ora Network Configuration File:/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
WU =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g2)(PORT = 1521))
)
)
SID_LIST_WU =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dg)
(ORACLE_HOME =/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1)
(SID_NAME = db1)
)
)
ADR_BASE_WU = /oracleapp/oinstall/oracle
[oracle@Oracle11g2admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
)
)
dg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg)
)
)
分别用tnsping命令测试监听服务是否可用，正常显示信息如下：
（tnsping对方的时候，有可能linux防火墙限制了，会提示TNS-12560: TNS: 协议适配器错误）
[oracle@Oracle11g2 dbs]$ tnsping dg
TNS Ping Utility for Linux: Version11.2.0.1.0 - Production on 27-MAY-2014 15:39:34
Copyright (c) 1997,2009,Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g2)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = dg)))
OK (10 msec)
[oracle@Oracle11g2 dbs]$ tnsping pri
TNS Ping Utility for Linux: Version11.2.0.1.0 - Production on 27-MAY-2014 15:40:16
Copyright (c) 1997,Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = Oracle11g1)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = pri)))
OK (10 msec)
[oracle@Oracle11g2 dbs]$
4.主库前期准备
设置强制写日志
[oracle@Oracle11g2 admin]$ sqlplus / as sysdba
sql>select FORCE_LOGGING fromv$database;
NO
sql> alter databaseforce logging;
sql> select FORCE_LOGGINGfrom v$database;
YES
5. 创建口令文件
通过dbca命令创建的数据库会自动在/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs目录下创建orapwdb1密码文件，也可以通过如下命令创建
[oracle@Oracle11g1 admin]$orapwd file=/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs/orapwdb1 password=oracle entries=5
密码文件为orapw（数据库ID），这里为orapwdb1.
6.修改主库初始化参数
[oracle@Oracle11g2dbs]$ vim initdb1.ora
*.DB_UNIQUE_NAME=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,dg)' #（启动db接受或发送redo data，包括所有库的db_unique_name）
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracleapp/oinstall/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=pri' #（主库归档目的地）
*.LOG_ARCHIVE_DEST_2= 'SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dg' #(当该库充当主库角色时，设置物理备库redo data的传输目的地)
*.LOG_ARCHIVE_MAX_PROCESSES=5 （最大ARCn进程数）
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE #允许redo传输服务传输数据到目的地
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE #允许redo传输服务传输数据到目的地
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc #配置日志格式
*.FAL_SERVER=dg #（配置网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）
*.FAL_CLIENT=pri #（配置网络服务名，fal_server拷贝丢失的归档文件到这里）
#*.DB_FILE_NAME_CONVERT='/路径','路径' （前为切换后的主库路径，后为切换后的备库路径，这里主备库目录结构完全一样，则无需设定）
#*.LOG_FILE_NAME_CONVERT='/路径','/路径' （同上，这两个名字转换参数是主备库的路径映射关系，可能会是路径全名，看情况而定）
*.STANDBY_FILE_MANAGEMENT=AUTO （auto后当主库的datafiles增删时备库也同样自动操作，且会把日志传送到备库standby_archive_dest参数指定的目录下，确保该目录存在，如果你的存储采用文件系统没有问题，但是如果采用了裸设备，你就必须将该参数设置为manual）
*.remote_login_passwordfile='EXCLUSIVE' #（exclusive or shared，所有库sys密码要一致，默认是exclusive）
[oracle@Oracle11g1 dbs]$sqlplus / as sysdba
创建主库spfile
sql>startup pfile='/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs/initdb1.ora';
sql> create spfile frompfile;
sql>SHUTDOWN IMMEDIATE;
sql>STARTUP MOUNT;
sql>ALTER DATABASEARCHIVELOG;
sql> ALTER DATABASE OPEN;
8. 创建备份库需要的控制文件
创建控制文件
sql>Shutdown immediate
sql>STARTUP MOUNT;
sql>ALTER DATABASECREATE STANDBY CONTROLFILEAS '/tmp/standby.ctl'; sql>ALTERDATABASE OPEN;
sql>shutdown immediate;
sql> startup
9. 备份生产数据库
[oracle@Oracle11g1 dbs] scp -rp /oracledata/db1 Oracle11g2:/oracledata/
[oracle@Oracle11g1 dbs]scp –rp /oracleapp/oinstall/oracle/admin/db1 Oracle11g2: /oracleapp/oinstall/oracle/admin (记得在备库创建admin)
在Oracle11g2上删掉数据库的控制文件control01.ctl
[oracle@Oracle11g2 ~]rm -rf/oracledata/db1/control01.ctl
将备份的控制文件拷贝到备份机器上,并复制两个备份control02.ctl,control03.ctl
[oracle@Oracle11g1 dbs] scp -rp /tmp/standby.ctl Oracle11g2:/oracledata/ db1/control01.ctl
在Oracle11g2上操作
[oracle@Oracle11g2 db1]$ cd/oracledata/db1/
[oracle@Oracle11g2 db1] cpcontrol01.ctl control02.ctl
[oracle@Oracle11g2 db1]cpcontrol01.ctl control03.ctl
将control02.ctl复制到/oracleapp/oinstall/oracle/flash_recovery_area/db1下
先建立db1文件夹
[oracle@Oracle11g2 db1]mkdir /oracleapp/oinstall/oracle/flash_recovery_area/db1
[oracle@Oracle11g2 dbs]cpcontrol02.ctl /oracleapp/oinstall/oracle/flash_recovery_area/db1/control02.ctl
10.修改备库pfile
将主库的orapwdb1,initdb1.ora文件拷贝到从库/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs下面
[oracle@Oracle11g1 dbs] scp -rp orapwdb1 Oracle11g2:/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs/orapwdb1
[oracle@Oracle11g1 dbs] scp -rp initdb1.ora Oracle11g2:/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs/initdb1.ora
[oracle@Oracle11g2 dbs]vim initdb1.ora
*.DB_UNIQUE_NAME=dg
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,ALL_ROLES)DB_UNIQUE_NAME=dg' #（主库归档目的地）
*.LOG_ARCHIVE_DEST_2= 'SERVICE=pri LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' #(当该库充当主库角色时，设置物理备库redo data的传输目的地)
*.LOG_ARCHIVE_MAX_PROCESSES=5 （最大ARCn进程数）
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE #允许redo传输服务传输数据到目的地
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE #允许redo传输服务传输数据到目的地
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc #配置日志格式
*.FAL_SERVER=pri #（配置网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件）
*.FAL_CLIENT=dg #（配置网络服务名，fal_server拷贝丢失的归档文件到这里）
#*.DB_FILE_NAME_CONVERT='/路径','/路径' （同上，这两个名字转换参数是主备库的路径映射关系，可能会是路径全名，看情况而定）
*.STANDBY_FILE_MANAGEMENT=AUTO （auto后当主库的datafiles增删时备库也同样自动操作，且会把日志传送到备库standby_archive_dest参数指定的目录下，确保该目录存在，如果你的存储采用文件系统没有问题，但是如果采用了裸设备，你就必须将该参数设置为manual）
*.remote_login_passwordfile='EXCLUSIVE' #（exclusive or shared，所有库sys密码要一致，默认是exclusive）
11.在备库上创建spfile
[oracle@Oracle11g2 dbs]$sqlplus / as sysdba
创建备库spfile
sql> Shutdown immediate
sql>startup pfile='/oracleapp/oinstall/oracle/product/11.2.0/dbhome_1/dbs/initdb1.ora';
sql> create spfile frompfile;
12.启动物理备用数据库
sql> Shutdown immediate
sql>STARTUP MOUNT;
13.配置standby redolog
（最佳性能模式可以忽略，如果要转为其它两种模式则要建立）
分部在Oracle11g1和Oracle11g2上建立standby文件夹，用于standby Redo log
[oracle@Oracle11g2 dbs]mkdir/oracledata/db1/standby
[oracle@Oracle11g1 dbs]mkdir/oracledata/db1/standby
在主库、从库上都配置standby redolog
sql> SELECT GROUP#,BYTES FROM V$LOG;
sql> SELECT GROUP#,BYTES FROMV$STANDBY_LOG;
创建日志组和redo log文件
sql> ALTER DATABASE ADD STANDBY LOGFILEgroup 4('//oracledata/db1/standby /slog1.rdo') SIZE 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILEgroup 5 ('/oracledata/db1/standby /slog2.rdo') SIZE 50M;
sql>ALTER DATABASE ADD STANDBY LOGFILEgroup 6 ('/oracledata/db1/standby /slog3.rdo') SIZE 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILEgroup 7('/oracledata/db1/standby /slog4.rdo') SIZE 50M;
standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。
14. Start Redo Apply
sql>ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
查看哪些归档日志被APPLY了 在备库
sql>SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BYSEQUENCE#;
在主库强制日志切换到当前的onlineredo log file.
sql> ALTER SYSTEMARCHIVE LOG CURRENT;
在备库查看新的被归档的redodata
sql>SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在备库查看接收到的被应用的redo
sql> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
查看数据库的角色
主库上：
sql>select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
------------------------------------ --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
备库上
sql>select database_role,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
------------------------------------ --------------------
PHYSICALSTANDBY MAXIMUM PERFORMANCE MAXIMUMPERFORMANCE
15、DataGuard关启状态
启用备用数据库
sql> STARTUP NOMOUNMT;
sql>alter database mount standby database;
sql>alter database recover managed standby database disconnect fromsession; （注： alter database recover managed standby database using current logfiledisconnect from session #切换到实时恢复管理模式）
sql>alter database recover managed standby database cancel;
sql>shutdown immediate;
从关闭状态打开
sql>startup nomount;
sql>alter database mount standby database;
sql>alter database open read only;
从正在恢复状态只读打开
sql>alter database recover managed standby database cancel;
sql >alter database open read only;
切换回到恢复状态
sql>alter database recover managed standby database disconnect from session;
16、主备数据库切换
16.１、正常切换：
主服务器
sql>alter database commit to switchover to physical standby;
sql>shutdown immediate
sql>startup nomount;
sql>alter database mount standby database;
sql>alter database recover managed standby database disconnect from session
备用服务器
sql>alter database commit to switchover to primary
sql>shutdown immediate;
sql>startup
16.２、非正常切换：（即主服务器当机的情况）启动failover
备服务器
sql>alter database recover managedstandby database finish;
sql>alter database commit to switchover to primary;
sql>shutdown immediate; sql >startup;
主备库切换
1）查看主库的状态
sql> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
2）将主库切换至备用模式
sql> ALTER DATABASE COMMIT TO SWITCHOVERTO PHYSICAL STANDBY WITH -
> SESSION SHUTDOWN;
sql> SHUTDOWN ABORT;
sql> STARTUP MOUNT;
4）查看备库准备向主库模式切换
sql> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
5）切换备库至主库模式
sql> ALTER DATABASE COMMIT TO SWITCHOVERTO PRIMARY WITH SESSION SHUTDOWN;
6）打开新的主数据库
sql> ALTER DATABASE OPEN;
7）在新的备库服务器上启动 REDO apply。
sql> ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE - > DISCONNECT FROM SESSION;
常用维护sql
添加几个常用命令
备库启动归档日志应用
alterdatabase recover managed standby database disconnect from session;
备库启动实时归档应用
alterdatabase recover managed standby database using current logfile disconnect fromsession
备库停止归档日志应用
alterdatabase recover managed standby database cancel;
查询归档日志是否被应用，查询V$archived_log视图的applied列
selectsequence#,dest_id,first_time,next_time,archvied,applied fromv$archived_log;
查看备库是否和主库同步，查询V$archive_dest_status视图
select archived_thread#,archived_seq#,applied_thread#,applied_seq#from v$archive_dest_status;
监控日志传送状态，V$archive_gap记录当前备库mrp进程恢复需要的但是还没有传到备库的日志更简单的是查看主备库的归档日志的序列号相差多少
select* from v$archive_gap;
查看当前主机的运行状态
selectswitchover_status,database_role,protection_mode from v$database
查看备库接收、应用redo数据的过程
selectmessage from v$dataguard_status
备库端查看RFS(Remote File Service)接收日志情况和MRP应用日志同步主数据库的情况(PhysicalStandby Database Only) 记录当前备库的一些进程情况和进程ID
selectprocess,status,thread#,sequence#,block#,blocks from v$managed_standby;
V$STANDBY_LOG备用数据库的备用日志的数量与当前状态等信息 SELECT * FROMV$STANDBY_LOG;
启动Data Guard 后， 查看同步情况：：
sql>select error from v$archive_dest;
用sql 查看了一下同步正常：
sql>select sequence#,applied from v$archived_log;
主库归档：
sql>ALTER SYSTEM SWITCH LOGFILE; --对单实例数据库或RAC中的当前实例执行日志切换
sql>ALTER SYSTEM ARCHIVE LOG CURRENT; --对数据库中的所有实例执行日志切换
在备库上，验证一下传过来的归档文件：
sql>SELECT SEQUENCE#,NEXT_TIME,APPLIED,completion_time FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
sql>SELECT THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
如过上面查到存在不连续的归档，那查找sequence 对应的归档文件：
sql>SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE#BETWEEN 7 AND 10;
sql>select distinct thread#,max(sequence#) over(partition by thread#) a fromv$archived_log;
在备库上，显示备库相关进程的当前状态信息：
sql>select process,client_process,status from v$managed_standby;
sql> select
dest_name,archived_thread#,applied_seq#,db_unique_name from v$archive_dest_status wherestatus='VALID';
检查应用模式(是否启用了实时应用)：
如果打开了实时应用，则recovery_mode会显示为：MANAGED REAL TIME APPLY。 sql> select dest_id,DEST_NAME,STATUS,TYPE,DATABASE_MODE,recovery_mode from v$archive_dest_status;
显示那些被自动触发写入alert.log或服务器trace文件的事件：
通常是在你不便访问到服务器查询alert.log时，可以临时访问本视图查看一些与dataguard 相关的信息。
sql>select * from v$dataguard_status;
灾难恢复（failover）
Step 1 Flush any unsent redo from theprimary database to the target standby database
sql> ALTER SYSTEM FLUSH REDO TOtarget_db_name;
Step 2 Verify that the standby database hasthe most recently archived redo log file for each primary database redothread.
sql> SELECT UNIQUE THREAD# AS THREAD,MAX(SEQUENCE#) - > OVER (PARTITION BY thread#) AS LAST fromV$ARCHIVED_LOG;
sql> ALTER DATABASE REGISTER PHYSICALLOGFILE 'filespec1';
Step 3 Identify and resolve any archivedredo log gaps.
sql> SELECT THREAD#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
sql> ALTER DATABASE REGISTER PHYSICALLOGFILE 'filespec1';
Step 4 Repeat Step 3 until all gaps areresolved.
Step 5 Stop Redo Apply.
Issue the following sql statement on thetarget standby database: sql> ALTER DATABASE RECOVER MANAGED STANDBYDATABASE CANCEL;
Step 6 Finish applying all received redodata.
Issue the following sql statement on thetarget standby database: sql> ALTER DATABASE RECOVER MANAGED STANDBYDATABASE FINISH;
Step 7 Verify that the target standbydatabase is ready to become a primary database.
Step 8 Switch the physical standby databaseto the primary role. Issue the following sql statement on the target standbydatabase:
sql> ALTER DATABASE COMMIT TO SWITCHOVERTO PRIMARY WITH SESSION SHUTDOWN;
Step 9 Open the new primary database.sql> ALTER DATABASE OPEN;
Step 10 Back up the new primary database.
Oracle recommends that a full backup betaken of the new primary database.
Step 11 Restart Redo Apply if it hasstopped at any of the other physical standby databases in your Data Guardconfiguration. For example:
sql> ALTER DATABASE RECOVER MANAGEDSTANDBY DATABASE USING CURRENT LOGFILE - > DISCONNECT FROM SESSION;
附录：
1. 启动到管理模式
sql>shutdown immediate;
sql>startup nomount;
sql>alter database mount standbydatabase;
sql>alter database recover managedstandby database disconnect from session;
2.启动到只读方式
sql>shutdown immediate;
sql>startup nomount;
sql>alter database mount standbydatabase;
sql>alter database open read only;
3如果在管理恢复模式下到只读模式
sql> recover managed standby databasecancel;
sql> alter database open read only;