oracle DG案例测试

来源：这里教程网时间：2026-03-03 17:21:20 作者：

测试环境： OS：HPUNIX 11.11 Primary： IP：10.134.196.123 SID：orapri Hostname：MPEDB DB_UNIQUE_NAME：orapri DBNAME=oradata Database：10.2.0.5(64) OS：HPUNIX 11.11 Primary： IP：10.134.196.124 SID：orapri Hostname：MSEDB DB_UNIQUE_NAME：orastd DBNAME=oradata Database：10.2.0.5(64) 实施DG前的准备工作装好备库数据库建实例:（windows） oradim -new -sid 实例名 linux 直接export=实例名就OK 开启数据库logging及数据库archivelog 开启数据库logging select FORCE_LOGGING from v$database; SQL> alter database force logging; 检查数据库是否开启archivelog SQL> archive log list; 如果数据库未开启archivelog，则要开启archivelog SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; 检查数据库是否开启archivelog SQL> archive log list; standby端： export ORACLE_SID=orapri 修改或新增listener.ora 和tnsnames.ora（或者用GUI工具配置）注意：listener.ora 中新增的部分在括号内而非括号外 primary端： listener.ora： SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/oracle/production/10.2) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = orapri) (ORACLE_HOME = /u01/oracle/production/10.2) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.196.123)(PORT = 1521)) ) tnsnames.ora orapri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.196.123)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orapri) ) ) orastd = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.196.124)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orastd) ) ) standby端： listener.ora: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/oracle/production/10.2) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = orapri) (ORACLE_HOME = /u01/oracle/production/10.2) (SID_NAME = orapri) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = MSEDB)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) ) tnsnames.ora orapri = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.196.123)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orapri) ) ) orastd = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.196.124)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orapri) ) ) 具体实施DG的步骤：在primary端修改相应参数 primary端： Oracle$sqlplus / as sysdba ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both; ALTER SYSTEM SET fal_server='orastd(網絡服務名)' scope=both; ALTER SYSTEM SET fal_client='orapri(網絡服務名)' scope=both; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orapri,orastd（是指數據庫的DB_UNIQUE_NAME）)' scope=both; ALTER SYSTEM SET log_archive_dest_1='LOCATION=/data1/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orapri' scope=both; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orastd(網絡服務名) LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orastd' scope=both; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both; #ALTER SYSTEM SET standby_archive_dest='/data1/stdlog' scope=both;(11g不需要) 在primary上生成pfile，并修改添加相应参数，生成standby需要的pfile primary端： oracle$>sqlplus / as sysdba SQL> CREATE PFILE='/export/home/oracle/standby.ora' FROM SPFILE; 將生成的pfile拷贝到standby端 scp /u01/oracle/production/10.2/dbs/file.ora oracle@10.134.196.124:/u01/oracle/production/10.2/dbs/file.ora 對照编辑生成的pfile文件('/export/home/oracle/standby.ora')，更改添加如下参数： *.db_unique_name='orastd' *.fal_server='orapri' *.fal_client='orastd' *.log_archive_config='DG_CONFIG=(orapri,orastd)' *.log_archive_dest_1='LOCATION=/data1/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orastd' *.log_archive_dest_2='SERVICE=orapri LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orapri' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.standby_archive_dest='/data1/archstd' (備幾端路徑) *.standby_file_management='AUTO' 创建pfile中相应目录（根据具体情况，primary和standby端要一致）創建數據文件需要的相應目錄（在主機數據庫中查看dba_data_files）主機備份控制文件成standby類型,備份完copy到備機器 alter database create standby controlfile as '/data1/rmanbk/control01.ctl' reuse; 在primary端用命令创建数据库密码文件，并接拷贝至standby端相同路径下（如已存在，可直接拷贝） primary端手工创建数据库密码文件 orapwd file='$ORACLE_HOME/dbs/orapw$ORACLE_SID' password=system entries=10 force=y primary端拷贝数据库密码文件至standby端 scp $ORACLE_HOME/dbs/orapw$ORACLE_SID oracle@10.134.196.124:$ORACLE_HOME/dbs 在standby端使用pfile启动实例 oradim -new -sid orapri----windows上執行 export ORACLE_SID=orapri--linux執行 sqlplus / as sysdba或sqlplus sys/Foxconn99 as sysdba sqlplus / as sysdba startup nomount pfile='$ORACLE_HOME/dbs/file.ora'; CREATE SPFILE FROM PFILE='$ORACLE_HOME/dbs/file.ora'; 在primary端开始使用rman备份数据库 primary端： rman target / backup full format='/data1/rmanbk/ora10g_%d_%T_%s' database include current controlfile for standby plus archivelog format='/data1/rmanbk/arch_%d_%T_%s'; 备份完毕后，把相应备份文件拷贝到丛库的相应目录（目录必须一致） scp /data1/rmanbk/* oracle@10.134.196.124:/data1/rmanbk/ 还原standby数据库 primary端： rman target / auxiliary sys/system@orastd duplicate target database for standby nofilenamecheck dorecover;（此法一般不用） catalog start with '備份文件'： #註冊備份（一般用這個） restore database; （這一步一般不做） //********** 收尾工作及开启dg standby端建立standby log（至少比redo多一组）： sqlplus / as sysdba ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/data1/stdlog/stdredo01.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/data1/stdlog/stdredo02.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/data1/stdlog/stdredo03.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/data1/stdlog/stdredo04.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/data1/stdlog/stdredo05.log') size 50M; ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/data1/stdlog/stdredo06.log') size 50M; ***************// （備庫應處於mount狀態）开启服务(standby端): SQL> alter database recover managed standby database disconnect from session; 關閉服务(standby端): SQL> alter database recover managed standby database cancel; 测试服务是否正常 standby端（看归档日志号）： SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; primary端（强制一个日志切换）： SQL> alter system switch logfile; standby端（看归档日志号）： SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; standby端（看应用归档日志的号）： SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 查看數據庫角色： select database_role from v$database; 查錯：select status,target,archiver,error,process from v$archive_dest; 实现primary、standby的切换： primary端： SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; primary端（上一步的结果必须是”TO STANDBY”才可以） SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; OR（上一步结果为”SESSIONS ACTIVE”,且解决不掉） SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Primary端： SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; standby端： SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; standby端（上一步的结果必须是”TO STANDBY”才可以）： SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; OR（上一步结果为”SESSIONS ACTIVE”，且解决不掉） SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; 打开新的primary： SQL>ALTER DATABASE OPEN; OR（最后一次启动后以read only模式打开过） SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP; 在新standby上重新启动log apply services（如果必要）： SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; OR(后台模式) SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; OR（实时应用redo） SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE; 新primary端（开始发送redo 数据到新standby端）： SQL> ALTER SYSTEM SWITCH LOGFILE; DG不能正常传archivelog 可能的原因及解决办法： 1，服务没开，重启服务； 2，主库密码变更，重新将主库密码文件copy到备库，重启备库； 3，监听问题