SERVER_2008_64_oracle_11g DG搭建笔记

1、oracle DG主库、备库安装路劲一样C:UsersAdministrator>sqlplus /nolog SQL> conn /as sysdba 已连接。#关闭数据库，将数据库修改为日志记录模式SQL> shutdown immediate;ORACLE 例程已经关闭。SQL> startup mount; 数据库装载完毕。SQL> alter database archivelog; 数据库已更改。SQL> alter database force logging; 数据库已更改。 #添加数据库日子记录文件SQL> select group#

2、,member from v$logfile order by group#;GROUP#-MEMBER1 D:ORACLEORADATADGREDO01.LOG2 D:ORACLEORADATADGREDO02.LOG3 D:ORACLEORADATADGREDO03.LOGSQL> alter database add standby logfile group 4 ('D:APPADMINISTRATORORADATAYKTCMSSTAN04.LOG') size 50m;SQL> alter database add standby logfile grou

3、p 5 ('D:APPADMINISTRATORORADATAYKTCMSSTAN05.LOG') size 50m;SQL> alter database add standby logfile group 6 ('D:APPADMINISTRATORORADATAYKTCMSSTAN06.LOG') size 50m; 数据库已更改。#创建控制文件SQL> alter database create standby controlfile as 'D:APPADMINISTRATORORADATAYKTCMSstandby.ctl'

4、; 数据库已更改。#创建参数文件SQL> create pfile='d:initora.ora' from spfile; 文件已创建。#编辑参数文件，initora.ora 添加DG 参数。添加内容如下。#DG_configdb_unique_name='primary' archive_lag_target=1800 FAL_SERVER=primary FAL_CLIENT=standbylog_archive_config='DG_CONFIG=(primary,standby)'log_archive_dest_1='L

5、OCATION=D:log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' LOG_ARCHIVE_DEST_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLE#修改后，通过参数文件启动主库SQL> shutdown immediate;

6、 ORA-01109: 数据库未打开已经卸载数据库。ORACLE 例程已经关闭。SQL> startup pfile='d:initora.ora' nomount;ORACLE 例程已经启动。Total System Global Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytesRedo Buffers 5021696 bytesSQL> create spfile from pfile='d:in

7、itora.ora' 文件已创建。SQL> shutdown immediate; ORA-01507: ?ORACLE 例程已经关闭。SQL> startup;ORACLE 例程已经启动。Total System Global Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytes Redo Buffers 5021696 bytes 数据库装载完毕。数据库已经打开。#创建密码文件,文件在D:appAdministrat

8、orproduct11.2.0dbhome_1databasePWD(DBname).ora#如果不存在就用CMD 执行命令创建,这个备库会用到。C:>orapwd file=D:appAdministratorproduct11.2.0dbhome_1databasePWDprimary.ora password=123456 entries=10#创建主库的监听LISTENERSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:ORACLEproduct11.2.0d

9、bhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:ORACLEproduct11.2.0dbhome_1binoraclr11.dll") )#主要用作监听里面的数据库服务 (SID_DESC = (GLOBAL_DBNAME = dg) (ORACLE_HOME = D:ORACLEproduct11.2.0dbhome_1) (SID_NAME = dg) ) )#创建主库的tnsnames(主库备库都填写上)STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRES

10、S = (PROTOCOL = TCP)(HOST = 192.168.18.1)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg) ) )PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.253)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg) ) )#启动监听程序,测试连接C:ls

11、nrctlLSNRCTL>start sqlplus sys/123456PRIMARY as sysdba#测试链接成功没问题后，关闭数据库。备库配置好启动后。再启动主库。SQL> shutdown immediate;-这里可以将数据库文件夹先复制到备库-#备库操作：将主库的文件复制备库1、 将D:oracle目录下admin、cfgtollogs、 diag和flash_recovery_area拷至备用库 的D:ORACLE目录下2、 将D:oracleoradatadg 拷贝到D:ORACLEoradatadg3、 将e:log目录拷至d盘下4、 将standby.ctl

12、拷至D:ORACLEoradatadg目录下.5、 initora.ora 复制到d盘6、 PWD(DBname).ora 复制到对应路径里面去#创建主库的监听LISTENER (跟主库的一样）#创建主库的tnsnames(跟主库的一样）#启动监听程序,测试连接C:lsnrctlLSNRCTL>start#在windows上注册oracle实例到服务C:>oradim -new -sid dg #修initora.ora改文件，注意将文件里面的控制文件路径改成复制过来的standby.ctl#DG_config db_unique_name='standby' ar

13、chive_lag_target=1800 FAL_SERVER=standby FAL_CLIENT=primarylog_archive_config='DG_CONFIG=(primary,standby)'log_archive_dest_1='LOCATION=D:log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'LOG_ARCHIVE_DEST_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMAR

14、Y_ROLE) DB_UNIQUE_NAME=primary'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLESTANDBY_FILE_MANAGEMENT=AUTOC:>set oracle_sid=dgC:>sqlplus /nolog SQL> conn /as sysdba 已连接到空闲例程。SQL> startup nomount pfile='D:initora.ora'ORACLE 例程已经启动。Total System Global Area 263049

15、216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytesRedo Buffers 5021696 bytes#启动节点SQL> create spfile from pfile='d:initora.ora' 文件已创建。SQL> shutdown immediate; ORA-01507: ?ORACLE 例程已经关闭。 SQL> startup nomount;ORACLE 例程已经启动。Total System Global

16、Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytesRedo Buffers 5021696 bytes#加截数据库为备用库模式SQL> alter database mount standby database; 数据库已更改。#关闭应用重做SQL>#alter database recover managed standby database cancel; 数据库已更改。 #开启备用库应用重做SQL>#alter d

17、atabase recover managed standby database disconnect from session;SQL>alter database recover managed standby database using current logfile disconnect from session; 数据库已更改。#已经配置完成，并启动了备库-已经配置完成#切换到主库。并启动SQL> startup-测试#在主库截断日志。SQL> alter system switch logfile; #查询主库的日志SEQSQL> select max(s

18、equence#) from v$archived_log;MAX(SEQUENCE#)- 21#切换到备库执行，看SEQ是否同步。SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)- 21#如果双方日子文件d:log文件夹不同步。可测试在主、备库之间是否能连接通。进行排查问题。C:sqlplus sys/123456standby as sysdbaC:sqlplus sys/123456primary as sysdba-日常维护-#查看DG 状态select name,open_mode,database_ro

19、le,protection_mode,SWITCHOVER_STATUS From v$database; -注意Data Guard 启动顺序：启动顺序：先standby ,后primary; 关闭顺序：先primary 后standby;在备库将实例启动到mount 状态：SQL> startup nomount;SQL>alter database mount standby database ; SQL>alter database recover managed standby database using current logfile disconnect fr

20、om session; 在主库将实例启动SQL> startup-#查询dg模式SQL> select protection_mode,protection_level from v$database; #模式切换SQL> shutdown immediateSQL> startup mount; SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=

21、standby' 最大保护模式：alter database set standby database to maximize protection; 最大可用模式：alter database set standby database to maximize availability; 最大性能模式：alter database set standby database to maximize PERFORMANCE; SQL> alter database open; #备库SQL> shutdown immediateSQL> startup nomount;

22、SQL> alter database mount standby database;SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' ; 最大保护模式：alter database set standby database to maximize protection; 最大可用模式：alter database set standby database to maximize availability; 最大性能模式：alter database set standby database to maximize PERFORMANCE; SQL>alter database recover managed standby database disconnect from session; -#删