oracle11g_windowsDG搭建

1、判断主库DG组件是否已经安装：

select * from v$option where parameter = 'Oracle Data Guard';

2、确认主库是否开启archivelog与force log

select log_mode,force_logging from v$database; --检查

alter database force logging; --开启

archive log list;

alter database archivelog;

3、配置备库监听

su - oracle

cd $ORACLE_HOME/network/admin

vi listener.ora

如下语句：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = D:\app\product\11.2.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = "EXTPROC_DLLS=ONLY:D:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")

)

(SID_DESC =

(GLOBAL_DBNAME = lisdb)

(ORACLE_HOME = D:\app\product\11.2.0\dbhome_1)

(SID_NAME = lisdb)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 77.169.210.10)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

ADR_BASE_LISTENER = D:\app

配置TNS服务

vi tnsnames.ora

LISDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 168.1.1.155)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = lisdb)

)

)

LISDBDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 77.169.210.10)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = lisdb)

)

)

4、配置主库standby redo log，并验证结果

查看是否已经存在standby redo log

select group#, thread#, sequence#, archived, status from v$standby_log;

根据以下sql查询的结果，创建standby redo log。

select GROUP#,THREAD#,BYTES from v$log ;

select MEMBER from v$logfile;

6组，1组200m

根据以上查询出的路径及group个数，创建standby redo log，创建原则thread要一样多，同个thread中要比redo多一个group（standby redo log大小必须与主库重做日志大小一致）

ALTER DATABASE ADD STANDBY LOGFILE 'D:\Oracle\oradata\lisdb\stbredo01.log' SIZE 200m;

ALTER DATABASE ADD STANDBY LOGFILE 'D:\Oracle\oradata\lisdb\stbredo02.log' SIZE 200m;

ALTER DATABASE ADD STANDBY LOGFILE 'D:\Oracle\oradata\lisdb\stbredo03.log' SIZE 200m;

ALTER DATABASE ADD STANDBY LOGFILE 'D:\Oracle\oradata\lisdb\stbredo04.log' SIZE 200m;

ALTER DATABASE ADD STANDBY LOGFILE 'D:\Oracle\oradata\lisdb\stbredo05.log' SIZE 200m;

ALTER DATABASE ADD STANDBY LOGFILE 'D:\Oracle\oradata\lisdb\stbredo06.log' SIZE 200m;

ALTER DATABASE ADD STANDBY LOGFILE 'D:\Oracle\oradata\lisdb\stbredo07.log' SIZE 200m;

检查创建standby redo log结果

select group#, thread#, sequence#, archived, status from v$standby_log;

5、配置主库初始化参数文件

alter system set log_archive_config='DG_CONFIG=(lisdb,lisdbdg)' scope=both sid='*';

alter system set log_archive_dest_1= 'location=d:\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=lisdb' scope=both sid='*';

alter system set log_archive_dest_2='SERVICE=LISDBDG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=lisdbdg' scope=both sid='*';

alter system set log_archive_dest_state_1=enable scope=both sid='*';

alter system set log_archive_dest_state_2=enable scope=both sid='*';

alter system set standby_file_management=auto scope=both sid='*';

alter system set fal_server='LISDBDG' scope=both sid='*';

alter system set fal_client='LISDB' scope=both sid='*';

6、备库的相应配置

将主库的密码文件、pfile拷贝到备库。

Pfile生成：

create pfile='C:\Users\Administrator\Desktop\lisdb.ora' from spfile;

将密码文件拷贝到备库：$ORACLE_HOME/database下

配置备库实例服务

oradim.exe -new -sid lisdb -startmode m

oradim.exe -edit -sid lisdb -startmode a

密码文件拷贝到备库后，需要修改文件名。格式为orapw+ORACLE_SID(windows路径在$ORACLE_HOME\database

主备库都测试联通性：

sqlplus sys/oracle@LISDB as sysdba

sqlplus sys/oracle@LISDBDG as sysdba

7、根据原库的pfile，修改参数，生成备库参数

set linesize 500 pages 0

col value for a90

col name for a50

select name, value

from v$parameter

where name in ('db_name','db_unique_name',

'log_archive_config',

'log_archive_dest_1','log_archive_dest_2',

'log_archive_dest_state_1',

'log_archive_dest_state_2',

'remote_login_passwordfile',

'log_archive_format',

'log_archive_max_processes',

'fal_server','db_file_name_convert',

'log_file_name_convert',

'standby_file_management')

/

原主库pfile：

lisdb.__db_cache_size=29930553344

lisdb.__java_pool_size=134217728

lisdb.__large_pool_size=671088640

lisdb.__oracle_base='D:\Oracle'#ORACLE_BASE set from environment

lisdb.__pga_aggregate_target=18790481920

lisdb.__sga_target=34896609280

lisdb.__shared_io_pool_size=0

lisdb.__shared_pool_size=3758096384

lisdb.__streams_pool_size=134217728

*.audit_file_dest='D:\Oracle\admin\lisdb\adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='D:\Oracle\oradata\lisdb\control01.ctl','D:\Oracle\fast_recovery_area\lisdb\control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='lisdb'

*.db_recovery_file_dest='D:\Oracle\fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='D:\Oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=lisdbXDB)'

*.enable_goldengate_replication=TRUE

*.fal_client='LISDB'

*.fal_server='LISDBDG'

*.log_archive_config='DG_CONFIG=(lisdb,lisdbdg)'

*.log_archive_dest_1='location=d:\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=lisdb'

*.log_archive_dest_2='SERVICE=LISDBDG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=lisdbdg'

*.memory_max_target=53687091200

*.memory_target=53687091200

*.open_cursors=5000

*.pga_aggregate_target=0

*.processes=1105

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=0

*.standby_file_management='AUTO'

*.undo_retention=36000

*.undo_tablespace='UNDOTBS1'

备库pfile：

*.__oracle_base='D:\app'#ORACLE_BASE set from environment

*.audit_file_dest='D:\Oracle\admin\lisdb\adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='D:\Oracle\oradata\lisdb\control01.ctl','D:\Oracle\oradata\lisdb\control02.ctl'

*.db_file_name_convert='D:\Oracle\oradata\lisdb\','D:\oracle\oradata\lisdb\datafile\','D:\xhlisdate\','D:\oracle\oradata\lisdb\datafile\'

*.db_block_size=8192

*.db_domain=''

*.db_name='lisdb'

*.db_unique_name='lisdbdg'

*.db_recovery_file_dest='D:\Oracle\fast_recovery_area'

*.db_recovery_file_dest_size=1000g

*.diagnostic_dest='D:\Oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=lisdbXDB)'

*.enable_goldengate_replication=TRUE

*.fal_client='LISDBDG'

*.fal_server='LISDB'

*.log_file_name_convert='D:\Oracle\oradata\lisdb\','D:\oracle\oradata\lisdb\logfile\'

*.log_archive_config='DG_CONFIG=(lisdb,lisdbdg)'

*.log_archive_dest_1='location=d:\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=lisdbdg'

*.log_archive_dest_2='SERVICE=LISDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=lisdb'

*.memory_max_target=13958643712

*.memory_target=13958643712

*.open_cursors=3000

*.pga_aggregate_target=0

*.processes=600

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=0

*.standby_file_management='AUTO'

*.undo_retention=36000

*.undo_tablespace='UNDOTBS1'

备库：编辑/tmp/orcl.ora，修改为以下内容：

*.db_file_name_convert='D:\Oracle\oradata\lisdb\','D:\oracle\oradata\lisdb\datafile\','D:\xhlisdate\','D:\oracle\oradata\lisdb\datafile\'

*.log_file_name_convert='D:\Oracle\oradata\lisdb\','D:\oracle\oradata\lisdb\logfile\'

D:\ORACLE\ORADATA\LISDB\

D:\XHLISDATE\

D:\ORACLE\ORADATA\LISDB\DATAFILE\

D:\ORACLE\ORADATA\LISDB\

D:\ORACLE\ORADATA\LISDB\LOGFILE\

用oracle用户在备库创建上面涉及到的路径（相关的路径配置，可根据实际情况修改）

D:\oracle\oradata\lisdb\datafile

8、备库以pfile创建spfile并启动数据库到nomount：

SQL> create spfile from pfile='D:\app\product\11.2.0\dbhome_1\database\INITlisdbpfile.ora';

SQL> startup nomount

9、备库：新建脚本duplicate.txt ，加入以下内容：

rman target sys/oracle@LISDB auxiliary sys/oracle@LISDBDG

run{

set newname for datafile 28 to "E:\APP\ADMINISTRATOR\ORADATA\ORCLOLD\DATAFILE\"SYSTEM_28.dbf;

set newname for datafile 29 to "E:\APP\ADMINISTRATOR\ORADATA\ORCLOLD\DATAFILE\"SYSAUX_29.dbf;

set newname for datafile 30 to "E:\APP\ADMINISTRATOR\ORADATA\ORCLOLD\DATAFILE\"UNDOTBS1_30.dbf;

set newname for datafile 31 to "E:\APP\ADMINISTRATOR\ORADATA\ORCLOLD\DATAFILE\"UNDO_3_31.dbf;

set newname for datafile 32 to "E:\APP\ADMINISTRATOR\ORADATA\ORCLOLD\DATAFILE\"UNDO_4_32.dbf;

set newname for datafile 34 to "E:\APP\ADMINISTRATOR\ORADATA\ORCLOLD\DATAFILE\"OGG_TBS_34.dbf;

set newname for datafile 36 to "E:\APP\ADMINISTRATOR\ORADATA\ORCLOLD\DATAFILE\"FUSE_36.dbf;

duplicate target database for standby from active database nofilenamecheck;

}

10、同步归档验证

以下为同步归档，预计时间比较久

SQL> alter database recover managed standby database disconnect from session;

使用11G Data Guard新特性，Archive Data Guard

在备库运行：

recover managed standby database cancel;

alter database open read only;

alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database parallel 4 using current logfile disconnect from session;

检验同步是否正常

在主库运行，确认是否有断层

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

STATUS GAP_STATUS

--------- ------------------------

VALID NO GAP

备库查询MRP进 程状态：

select inst_id,process,status,thread#,sequence#,block# from gv$managed_standby where PROCESS like 'MRP%';

备库查询同步延迟

SQL> select value from v$dataguard_stats where name='apply lag' ;

VALUE

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

+00 00:00:00 （查询时间为000，表示同步无延迟）

主库创建临时表确认是否正常同步

create table test2020 as select * from dual;

select * from test2020;

切换前检验是否同步正常

alter system switch logfile;

主库：

--检查主库已经产生的日志sequence# 号

select thread#,max(sequence#) "Last Primary Seq Generated" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;

备库：

-- 检查备库已经接收到的 sequence# 号

select thread#,max(sequence#) "Last Standby Seq Received" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;

-- 检查备库已经应用到的 sequence# 号

select thread#,max(sequence#) "Last Standby Seq Applied" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

11、备库switch over与failover

alter database recover managed standby database cancel;

alter database recover managed standby database finish force；

//在备库上操作

alter database commit to switchover to primary with session shutdown;

alter database commit to switchover to primary;

alter database open;

select switchover_status,database_role,open_mode from v$database;