oracle-11g-Dataguard详细配置手册

1、Oracle 11G Dataguard详细配置手册版本： V1 作者：知行合一、环境描述：主库：IP 地址： 7OS：RHEL 6.5 X64Hostname :lin-1Oracle 版本： Oracle_home: /u01/app/oracle/product/11.2.0/db_1SQL show parameter name;NAMETYPEVALUEdb_ db_namestringdb_unique_name global_names instance_name lock_name_space log_ service_namesstrin

2、g string boolean string stringorclorclFALSE orclstringstringorcl备库：IP 地址： 8OS：RHEL 6.5 X64Hostname :lin-2Oracle 版本： Oracle_home: /u01/app/oracle/product/11.2.0/db_1SQL show parameter name;NAMETYPEVALUEdbstringdb_namedb_unique_nameglobal_namesstring string booleanorcl orcl2FALSEins

3、tance_name lock_name_space log_ service_namesstringstringorclstringstringorcl说明： 由于我采用的是 vmware 虚拟化环境进行测试的。 测试时首先安装好一台数据库， 然 后再克隆出另外一套。第二套根据需要，对主机名、 IP 地址进行更改。另外不要忘记更换 oracle 用户的环境变量、更改 /etc/hosts 解析文件 注意：主库和备库的 db_unique_name 不能相同二：修改主备库 listener.ora ， tnsnames.ora2.1、主库配置2.1.1 、配置主库的监听：oraclelin-1

4、 admin$ vi listener.ora(G# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =(SID_LIST = (SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = o

5、rcl)LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (ADDRESS = (PROTOCOL = TCP)(HOST = lin-1)(PORT = 1521) )ADR_BASE_LISTENER = /u01/app/oracle2.1.2 、修改主库的 TnsnameORCL =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lin-1)(PORT = 1521) (CONNECT_DATA =(SERV

6、ER = DEDICATED)(SERVICE_NAME = orcl)ORCL2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lin-2)(PORT = 1521) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)2.2、备库配置2.2.1 、配置备库的监听监听： oraclelin-2 admin$ cat listener.oraFile:# listener.ora Network Configuration/u01/app/oracle/product/11.2.

7、0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =(SID_LIST = (SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl)LISTENER =(DESCRIPTION_LIST = (DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (

8、ADDRESS = (PROTOCOL = TCP)(HOST = lin-2)(PORT = 1521) )ADR_BASE_LISTENER = /u01/app/oracle2.1.2 、修改备库的 TnsnameORCL = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lin-1)(PORT = 1521)(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)ORCL2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = l

9、in-2)(PORT = 1521) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)、主库前期准备设置强制写日志SQL select FORCE_LOGGING from v$database; NOSQL alter database force logging;SQL select FORCE_LOGGING from v$database; YES四 、 创建口令文件orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=5五、修改主库初始化参数创建主

10、库 pfilesql create pfile from spfile;修改主库 pfile ： 在 pfile 中添加如下内容： DB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,orcl2)VALID_FOR=(ALL_LOG)LOG_ARCHIVE_DEST_1=LOCATION=/u01/app/archiveDB_UNIQUE_NAME=orclLOG_ARCHIVE_DEST_2= SERVICE=orcl2DB_UNIQUE_NAME=orcl2LGWR ASYNC VALID_FOR=(ONLINE_LOG)No Arc

11、hive ModeDisabledArchive destinationUSE_DB_RECOVERY_LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_FORMAT=%t_%s_%r.arcFAL_SERVER=orcl2FAL_CLIENT=orclSTANDBY 把修改后的 pfile 也拷贝到备库上：scp-p/uO1/a pp/oracle/product/11.2.0/db_1/dbs/i nitorcl.oralin-2:/u01/app/oracle/product/11.2.0/

12、db_1/dbs/ 注意：根据实际情况更改用户的环境变量六.修改主库运行在归档模式下SQLarchive log list; Database log mode Automatic archivalOldest online log sequenceCurrent log sequenceSQLalter system set LOG_ARCHIVE_DEST_1= LOCATION=/u01/app/archive;System altered.SQLshutdown immediate;Database closed.Database dismounted.ORACLE instance

13、shut down.SQL startup mount;ORACLE instance started.Total System Global Area 1570009088 bytes Database mounted.Fixed SizeVariable SizeDatabase BuffersRedo Buffers2213696 bytes922749120 bytes637534208 bytes7512064 bytesSQL alter database archivelog;Database altered.SQLalter database open;Database alt

14、ered.SQL七 . 创建备份库需要的控制文件在主库创建备库控制文件 注：以下操作在主库上进行SQLShutdown immediate;SQLSTARTUP MOUNT;SQLALTER DATABASE CREATE STANDBY CONTROLFILE AS /tmp/orcl.ctl;SQLALTER DATABASE OPEN;创建主库 spfileSQLShutdown immediateSQLstartup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.orasql create sp pfile=/u01/ap

15、p/oracle/product/11.2.0/db_1/dbs/initorcl.ora;关闭数据库sql shutdown immediate;八 . 备份生产数据库并把数据复制到对端说明： 这里采用关闭主库数据库， 进行冷备份的方法进行备份， 数据库的控制文件和数据文 件全部位于 /u01/app/oracle/oradata/orcl/ 目录scp -rp /u01/app/oracle/oradata/orcl/* lin-2:/u01/app/oracle/oradata/orcl/九 .修改备库 pfileoraclelin-2 dbs$ cat initorcl.ora orc

16、l._db_cache_size=637534208 orcl._java_pool_size=16777216orcl._large_pool_size=16777216orcl._oracle_base=/u01/app/oracle#ORACLE_BASE set from environment orcl._pga_aggregate_target=637534208orcl._sga_target=939524096orcl._shared_io_pool_size=0orcl._shared_pool_size=251658240orcl._streams_pool_size=0

17、*.audit_/u01/app/oracle/admin/orcl/adump*.audit_trail=db *.compatible=.0*.control_files=/u01/app/oracle/oradata/orcl/control01.ctl*.db_block_size=8192 *.db_domain= *.db_name=orcl *.db_recovery_/u01/app/oracle/flash_recovery_area *.db_recovery_*.diagnostic_dest=/u01/app/oracle *.dispatchers=(

18、PROTOCOL=TCP) (SERVICE=orclXDB)*.memory_target=1562378240 *.open_cursors=300*.processes=150*.remote_login_passwordfile=EXCLUSIVE *.undo_tablespace=UNDOTBS1 *.DB_UNIQUE_NAME=orcl2 *.LOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,orcl2)*.LOG_ARCHIVE_DEST_1=DB_UNIQUE_NAME=orcl2VALID_FOR=(ALL_LOG)*.LOG_ARCHIVE_DEST

19、_2=DB_UNIQUE_NAME=orclSERVICE=orclLGWR ASYNC VALID_FOR=(ONLINE_LOG)*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc*.FAL_SERVER=orcl*.FAL_CLIENT=orcl2*.STANDBY、将控制文件传输到对端面命令在主库上执行：scp -rp /tmp/orcl.ctl lin-2:/u01/app/oracle/oradata/orcl/传输到对端后，并更改控制

20、文件的名称LOCATION=/u01/app/archive在备库上修改名称为 control01.ctoraclelin-2 orcl$ mv orcl.ctl control01.ctloraclelin-2 orcl$ lscontrol01.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf、在备库上创建口令文件orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=5、

21、在备库上创建 spfile在备库上，根据第九章创建的 pfile 生成 spfileSQLShutdown immediateSQLstartup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.oraSQLCREATE SPFILE FROM PFILE;三、启动物理备用数据库SQLSTARTUP MOUNT;四、配置 Standby Redo Log在两边都配置 standby redo log 在主库查看日志组的数量和每个日志文件的大小 SQL SELECT GROUP#, BYTES FROM V$LOG;在备库库查看日志组

22、的数量和每个日志文件的大小SQL SELECT GROUP#, BYTES FROM V$STANDBY_LOG;在主备库上创建日志组和 redo log 文件SIZE 50M;SIZE 50M;SIZE 50M;SQLALTERDATABASEADDSTANDBYLOG4(/u01/app/oracle/oradata/orcl/stdby_redo04.log)SQLALTERDATABASEADDSTANDBYLOG5(/u01/app/oracle/oradata/orcl/stdby_redo05.log)SQLALTERDATABASEADDSTANDBYLOG6(/u01/app

23、/oracle/oradata/orcl/stdby_redo06.log)五、配置闪回数据库说明： 这一步为可选操作， 但强烈建议开启数据库闪回功能。 闪回允许你将数据库还原到以 前的某一时间点。 当发生故障转移时， 这个功能非常有用， 它能让你将老的主库闪回到故障 前，然后将其转换为备库。如果没有启用闪回功能，你就必须重建备库，意味着要再复制一 次数据文件。除了这个好处，闪回还能在某些情况下让你避免从备份恢复数据。新路径 ;(1) 快速恢复区 (Flash/Fast Recovery Area) ，默认是配置的 , 但是需要确认这个区域的磁盘 够大，至少 50G 以上(默认 3G) sql

24、 show parameter db_recovery_ 可以修改位置： sql alter system set db_recovery_ 更改大小： sql alter system set db_recovery_G;(2) 查看是否启用，默认是不开启的 sql select flashback_on from v$database; 开启闪回： sql alter database flashback on; 如果你碰到 ORA-01153 报错，那一定是在备库进行此操作。你需要先取消重做日志应用， 启用闪回日志，然后重新启用日志应用。 在主库启用闪回日志，不会同步备库也启用。你必须手

25、动在主库和备库上均启用闪回日志。 如果不启用闪回日志，当出现故障转移时，你将需要完全重新开始创建一个备库。六、Start Redo Apply在备库上执行：sql ALTERDATABASERECOVERMANAGESDTANDBYDATABASEUSINGCURRENLTOGFROMSESSION;查看哪些归档日志被 APPLY了在备库上执行：sql SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在主库强制日志切换到当前的 online redo log file. sql ALTER SYSTEM ARCHI

26、VE LOG CURRENT; 在备库查看新的被归档的 redo data sql SELECTSEQUENCE#F,IRST_TIME, NEXT_TIMEFROMV$ARCHIVED_LOOGRDERBY SEQUENCE#; 在备库查看接收到的被应用的 redoSQL SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 查看数据库的角色sql select database_role,protection_mode,protection_level from v$database;附件：日常维护主备库切换1.

27、查看主库的状态SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;2. 将主库切换至备用模式SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH - SESSION SHUTDOWN;3. 关闭、装载主数据库SQL SHUTDOWN ABORT;SQL STARTUP MOUNT;4. 查看备库准备向主库模式切换SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUSTO_PRIMARY 1 row select

28、ed5. 切换备库至主库模式SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;6. 打开新的主数据库SQL ALTER DATABASE OPEN;7. 在新的备库服务器上启动 REDO apply 。SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE - DISCONNECT FROM SESSION;灾难恢复( failover )Step 1 Flush any unsent redo from t

29、he primary database to the target standby databaseSQL ALTER SYSTEM FLUSH REDO TO target_db_name;Step 2 Verify that the standby database has the most recently archived redo log each primary database redo thread.SQL SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) - OVER (PARTITION BY thread#) AS LAST

30、from V$ARCHIVED_LOG;SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE ;Step 3 Identify and resolve any archived redo log gaps.SQL SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE ;Step 4 Repeat Step 3 until all gaps are resolved.Step 5 Stop Red

31、o Apply.Issue the following SQL statement on the target standby database:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Step 6 Finish applying all received redo data.Issue the following SQL statement on the target standby database:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;Step 7 Verify that the target standby database is ready