GD搭建

 背景：部门oracle技术大神，实操现场演示，DG部署搭建

实操步骤如下：

1、环境说明

主机	IP	SID	DB_UNIQUE_NAME	数据库版本
ora01	192.168.xx.xx	dg1	dg1	11.2.0.4
ora02	192.168.xx.xx	dg1	dg2	11.2.0.4

注意：DB_UNIQE_NAME要不一样

2、搭建前工作检查

检查主库是否归档，是否开启强制归档

select name,open_mode,database_role,log_mode,force_logging from v$database;

--关闭数据库

shutdown immediate;

--启动到mount状态

startup mount;

--开启归档

alter database archivelog;

--打开数据库

alter database open;

--开启强制归档

alter database force logging;

3、检查目录，并在备库上创建相应的目录

创建归档目录，在主备上都创建

mkdir /u01/app/oracle/archivelog

在主库上查看控制文件目录，并在备库上查看是否有对应目录

查看数据文件目录，并在备库上查看是否有对应目录

查看参数文件、密码文件目录

一般在$ORACLE_HOME/dbs/下

创建rman备份目录，由于不停机搭建，采用rman热备，备库相同创建

mkdir  -p /u01/app/backup/rman

4、添加tnsname和监听配置，在主备库都增加

--查看主库tns(备库也是一样的)

--查看主库listener(备库也是一样的)

5、修改主库参数 ---这里的dg1和dg2都是tns中的名称

--db_config配置为主备库db_unique_name

alter system set log_archive_config='dg_config=(dg1,dg2)'; 

alter system set log_archive_dest_1='location=/data/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=dg1';

  --service配置为tns中的备库名称

alter system set log_archive_dest_2='service=dg2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dg2';

--fal配置为tns中的名称

alter system set fal_server=dg2;

alter system set fal_client=dg1;

alter system set standby_file_management=auto;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

--主备库数据文件、日志文件存放路径不一致的时候，进行转换映射

db_file_name_convert

log_file_name_convert

ALTER SYSTEM SET log_file_name_convert='/opt/app/oracle/oradata/dg1/','/opt/app/oracle/oradata/dg1/' scope=spfile;

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/opt/app/oracle/oradata/dg1/','/opt/app/oracle/oradata/dg1/' scope=spfile;

6、在主库创建profile

create pfile from spfile;

7、在主库备份rman

rman target /

执行以下：

run

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/data/backup/rman/ctl_%F';

backup database format '/data/backup/rman/full_%d_%T_%u.bak';

backup archivelog all format '/data/backup/rman/arc_%s_%p_%t.bak';

release channel c1;

release channel c2;

}

8、复制参数文件、密码文件、rman备份集、控制文件到备库

scp initora11.ora orapwora11 192.168.17.145:/u01/app/oracle/product/11.2.0/db_1/dbs/

--复制监听和tns到备库，并修改

scp tnsnames.ora listener.ora 192.168.17.145:/u01/app/oracle/product/11.2.0/db_1/network/admin/

scp -r /u01/app/backup/rman/* 192.168.17.145:/u01/app/backup/rman/

9、修改备库参数文件     注意：到备库后标红色参数都要修改和主库是相反的了,这里我已经改了

[oracle@ora02 dbs]$ cat initora11.ora

ora11.__db_cache_size=654311424

ora11.__java_pool_size=16777216

ora11.__large_pool_size=33554432

ora11.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

ora11.__pga_aggregate_target=654311424

ora11.__sga_target=956301312

ora11.__shared_io_pool_size=0

ora11.__shared_pool_size=234881024

ora11.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='dg1'

*.db_unique_name='dg2'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

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

*.fal_client='dg2'

*.fal_server='dg1'

*.log_archive_config='dg_config=(dg1,dg2)'

*.log_archive_dest_1='location=/u01/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=dg2'

*.log_archive_dest_2='service=dg1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dg1'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

*.memory_target=1604321280

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

 

10、在备库上启动监听

lsnrctl start;

登录备库，生成动态参数文件

create spfile from pfile;

启动到nomount状态

startup nomount;

11、在主库上登录rman恢复，备库恢复后数据库状态就变成mount

rman target /

connect auxiliary sys/MCgyWLnRHEu7@dg2 --这个地方应该提示是一个空实例数据库

duplicate target database for standby nofilenamecheck;

12、在主备库上增加日志（日志大小一致）standby的日志要比日志组多一个

select a.group#,a.member,b.bytes/1024/1024 size_m from v$logfile a,v$log b

where a.group#=b.group#;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/opt/app/oracle/oradata/dg1/sty_redo04.log' size 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/opt/app/oracle/oradata/dg1/sty_redo05.log' size 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/opt/app/oracle/oradata/dg1/sty_redo06.log' size 200M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/opt/app/oracle/oradata/dg1/sty_redo07.log' size 200M;

select group#,member member from v$logfile;

13、在备库上打开数据库，并应用日志

ALTER DATABASE OPEN;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

select t.name,t.open_mode,t.database_role,t.db_unique_name from v$database t;

14、验证

--看是否有延迟

select * from v$dataguard_stats;

--查看两表sequence是否一致

select t.sequence#,t.first_time,t.name from v$archived_log t  where t.applied='YES'order by t.first_time desc;

select max(t.sequence#) from v$archived_log t where t.applied='YES';