Oracle dataguard 11G 配置与维护手册.docx

Oracle 11g R2 Dataguard 配置手册1判断DataGuard是否安装select * from v$option where parameter = Oracle Data Guard;2. 网络配置192.168.1.10（orcl）-192.168.1.20(dg)3.监听配置主库oraclenode1 $ cd /u01/app/product/11.2.0/db_1/network/adminoraclenode1 admin$ cat listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521) ) )oraclenode1 admin$ cat tnsnames.oraORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = dg) ) )备库oraclenode1 admin$ cat listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521) ) )oraclenode1 admin$ cat tnsnames.oraORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = dg) ) )4.主库前期准备设置强制写日志SQL select FORCE_LOGGING from v$database;NOSQL alter database force logging;SQL select FORCE_LOGGING from v$database;YES5. 创建口令文件orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=oracle entries=56.修改主库初始化参数 创建主库pfile sql create pfile from spfile;修改pfileDB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,dg) LOG_ARCHIVE_DEST_1= LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl LOG_ARCHIVE_DEST_2= SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=dgFAL_CLIENT=orclSTANDBY_FILE_MANAGEMENT=AUTO Pfile 拷贝到备库上scp rp /u01/app/product/11.2.0/db_1/dbs/initorcl.ora node2:/u01/app/product/11.2.0/db_1/dbs/7.修改数据库运行在归档模式下SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;8. 创建备份库需要的控制文件创建控制文件Shutdown immediateSTARTUP MOUNT; ALTER DATABASE CREATE STANDBY CONTROLFILE AS /tmp/orcl.ctl; ALTER DATABASE OPEN;创建主库pfileShutdown immediatestartup pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.orasql create spfile from pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora;(先把原来的干掉)shutdown immediate;startup9. 备份生产数据库 scp -rp /u01/oradata/ORCL node2:/u01/oradata/scp rp /u01/app/admin/orcl node2:/u01/app/admin(记得在备库创建admin)10.修改备库pfileDB_UNIQUE_NAME=orcl LOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,dg) LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc LOG_ARCHIVE_DEST_1= LOCATION=/u01/app/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg LOG_ARCHIVE_DEST_2= SERVICE=orcl ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=orcl LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE FAL_SERVER=orclFAL_CLIENT=dgSTANDBY_FILE_MANAGEMENT=AUTO11.将控制文件scp -rp /tmp/dg.ctl node2:/u01/oradata/ORCL/controlfile/scp -rp /tmp/dg.ctl node2:/u01/flash_recovery_area/orcl/controlfile/12.在备库上创建口令文件orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=oracle entries=513.在备库上创建spfile Shutdown immediatestartup pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.oraCREATE SPFILE FROM PFILE;14.启动物理备用数据库STARTUP MOUNT;15配置Standby Redo Log在两边都配置standby redo log在主库查看日志组的数量和每个日志文件的大小SQL SELECT GROUP#, BYTES FROM V$LOG;在备库库查看日志组的数量和每个日志文件的大小SQL SELECT GROUP#, BYTES FROM V$STANDBY_LOG;创建日志组和redo log文件SQL ALTER DATABASE ADD STANDBY LOGFILE group 4(/oracle/dbs/slog1.rdo) SIZE 50M; SQL ALTER DATABASE ADD STANDBY LOGFILE group 5 (/oracle/dbs/slog2.rdo) SIZE 50M;SQLALTER DATABASE ADD STANDBY LOGFILE group 6 (/oracle/dbs/slog3.rdo) SIZE 50M;SQL ALTER DATABASE ADD STANDBY LOGFILE group 7(/oracle/dbs/slog4.rdo) SIZE 50M;16. Start Redo ApplyALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; 查看哪些归档日志被APPLY了在备库SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在主库强制日志切换到当前的online redo log file.ALTER SYSTEM ARCHIVE LOG CURRENT;在备库查看新的被归档的redo dataSELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在备库查看接收到的被应用的redoSQL SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;查看数据库的角色select database_role,protection_mode,protection_level from v$database;l 主备库切换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;l 灾难恢复（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 Verify that the target standby database is ready to become a primarydatabase.Step 8 Switch the physical standby database to the primary role.Issue the following SQL statement on the target standby database:SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;Step 9 Open the new primary database.SQL ALTER DATABASE OPEN;Step 10 Back up the new primary database.Oracle reco