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

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;NAME TYPE VALUE- - -db_file_name_convert stringdb_name string orcldb_unique_name string orclglobal_names boolean FALSEinstance_name string orcllock_name_space stringlog_file_name_convert stringservice_names string orcl备库：IP地址：8OS：RHEL 6.5 X64Hostname :lin-2Oracle 版本：Oracle_home: /u01/app/oracle/product/11.2.0/db_1SQL show parameter name;NAME TYPE VALUE- - -db_file_name_convert stringdb_name string orcldb_unique_name string orcl2global_names boolean FALSEinstance_name string orcllock_name_space stringlog_file_name_convert stringservice_names string orcl说明：由于我采用的是vmware 虚拟化环境进行测试的。测试时首先安装好一台数据库，然后再克隆出另外一套。第二套根据需要，对主机名、IP地址进行更改。另外不要忘记更换oracle 用户的环境变量、更改/etc/hosts解析文件注意：主库和备库的db_unique_name不能相同二：修改主备库listener.ora，tnsnames.ora2.1、主库配置2.1.1、配置主库的监听：oraclelin-1 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 = orcl) ) ) 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 = (SERVER = 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.ora# 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 = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (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 = lin-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五、修改主库初始化参数创建主库pfile sql create pfile from spfile;修改主库pfile：在pfile 中添加如下内容：DB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,orcl2) LOG_ARCHIVE_DEST_1= LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl LOG_ARCHIVE_DEST_2= SERVICE=orcl2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2 LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orcl2FAL_CLIENT=orclSTANDBY_FILE_MANAGEMENT=AUTO把修改后的pfile 也拷贝到备库上：scp rp /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora lin-2:/u01/app/oracle/product/11.2.0/db_1/dbs/注意：根据实际情况更改用户的环境变量六.修改主库运行在归档模式下SQL archive log list;Database log mode No Archive ModeAutomatic archival DisabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 4Current log sequence 6SQL alter system set LOG_ARCHIVE_DEST_1= LOCATION=/u01/app/archive;System altered.SQL shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL startup mount;ORACLE instance started.Total System Global Area 1570009088 bytesFixed Size 2213696 bytesVariable Size 922749120 bytesDatabase Buffers 637534208 bytesRedo Buffers 7512064 bytesDatabase mounted.SQL alter database archivelog;Database altered.SQL alter database open;Database altered.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 spfile from pfile= /u01/app/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.oraorcl._db_cache_size=637534208orcl._java_pool_size=16777216orcl._large_pool_size=16777216orcl._oracle_base=/u01/app/oracle#ORACLE_BASE set from environmentorcl._pga_aggregate_target=637534208orcl._sga_target=939524096orcl._shared_io_pool_size=0orcl._shared_pool_size=251658240orcl._streams_pool_size=0*.audit_file_dest=/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_file_dest=/u01/app/oracle/flash_recovery_area*.db_recovery_file_dest_size=4070572032*.diagnostic_dest=/u01/app/oracle*.dispatchers=(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= LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl2*.LOG_ARCHIVE_DEST_2= SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl*.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_FILE_MANAGEMENT=AUTO十、将控制文件传输到对端下面命令在主库上执行：scp -rp /tmp/orcl.ctl lin-2:/u01/app/oracle/oradata/orcl/传输到对端后，并更改控制文件的名称在备库上修改名称为control01.ct oraclelin-2 orcl$ mv orcl.ctl control01.ctloraclelin-2 orcl$ lscontrol01.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbfredo01.log redo03.log system01.dbf undotbs01.dbf十一、在备库上创建口令文件orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=5十二、在备库上创建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;在备库库查看日志组的数量和每个日志文件的大小SQL SELECT GROUP#, BYTES FROM V$STANDBY_LOG;在主备库上创建日志组和redo log文件SQLALTER DATABASE ADD STANDBY LOGFILE group 4(/u01/app/oracle/oradata/orcl/stdby_redo04.log) SIZE 50M;SQLALTER DATABASE ADD STANDBY LOGFILE group 5(/u01/app/oracle/oradata/orcl/stdby_redo05.log) SIZE 50M;SQLALTER DATABASE ADD STANDBY LOGFILE group 6(/u01/app/oracle/oradata/orcl/stdby_redo06.log) SIZE 50M;十五、配置闪回数据库说明：这一步为可选操作，但强烈建议开启数据库闪回功能。闪回允许你将数据库还原到以前的某一时间点。当发生故障转移时，这个功能非常有用，它能让你将老的主库闪回到故障前，然后将其转换为备库。如果没有启用闪回功能，你就必须重建备库，意味着要再复制一次数据文件。除了这个好处，闪回还能在某些情况下让你避免从备份恢复数据。(1)快速恢复区(Flash/Fast Recovery Area)，默认是配置的,但是需要确认这个区域的磁盘够大，至少50G以上（默认3G）sqlshow parameter db_recovery_file_dest可以修改位置：sqlalter system set db_recovery_file_dest=新路径;更改大小：sqlalter system set db_recovery_file_dest_size=40G;(2)查看是否启用，默认是不开启的sqlselect flashback_on from v$database;开启闪回：sqlalter database flashback on;如果你碰到 ORA-01153 报错，那一定是在备库进行此操作。你需要先取消重做日志应用，启用闪回日志，然后重新启用日志应用。在主库启用闪回日志，不会同步备库也启用。你必须手动在主库和备库上均启用闪回日志。如果不启用闪回日志，当出现故障转移时，你将需要完全重新开始创建一个备库。十六、 Start Redo Apply在备库上执行：sqlALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 查看哪些归档日志被APPLY了在备库上执行：sqlSELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在主库强制日志切换到当前的online redo log file.sqlALTER SYSTEM ARCHIVE LOG CURRENT;在备库查看新的被归档的redo datasqlSELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在备库查看接收到的被应用的redoSQL SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;查看数据库的角色sqlselect database_role,protection_mode,protection_level from v$database;附件：日常维护主备库切换1.查看主库的状态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_STATUS-TO_PRIMARY1 row selected5.切换备库至主库模式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 the primary database to the target standbydatabaseSQL ALTER SYSTEM FLUSH REDO TO target_db_name;Step 2 Verify that the standby database has the most recently archived redo logfile for each primary database redo thread.SQL SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) - OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE filespec1;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 filespec1;Step 4 Repeat Step 3 until all gaps are resolved.Step 5 Stop Redo 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 Verif