目录
一、服务器环境
1、操作系统
Oracle Linux 7.6
2、数据库版本
Oracle 11.2.0.4.0
二、DG搭建要求
1、db_name:
mesorcl(主备一致)
2、db_unique_name:
主库:mesorcl 备库:mesorclstd
3、instance_name:
mesorcl,mesorcl(主备一致)
三、主库配置
1、启动归档日志
(1)启动归档需要在mount阶段
shutdown immediate; startup mount; alter database archivelog; alter database open;
(2)增大恢复区空间:
SQL>alter system set db_recovery_file_dest_size=200G;
2、查看force logging是否开启并启动
select name,log_mode,force_logging from v$database; alter database force logging;
3、增加standby的redolog
查看日志文件获取redolog文件路径和大小,保证主备redolog的大小是一致的。
(1)查看当前logfile的数量及大小
SQL>select member from v$logfile;
SQL>select bytes/1024/1024 as filesize from v$log;
(2)增加standby的logfile(standby logfile的要求)
①大小与主机大小一致。
②文件数量:
Standby redo log数量>=(每个instance日志组个数+1)*instance实例个数。我们通常为单实例:Standby redo log数量=(3+1)*1=4即可。
alter database add standby logfile group 6 '/oracle/app/oracle/oradata/mesorcl/std_redo06.log' size 200m; alter database add standby logfile group 7 '/oracle/app/oracle/oradata/mesorcl/std_redo07.log' size 200m; alter database add standby logfile group 8 '/oracle/app/oracle/oradata/mesorcl/std_redo08.log' size 200m; alter database add standby logfile group 9 '/oracle/app/oracle/oradata/mesorcl/std_redo09.log' size 200m; alter database add standby logfile group 10 '/oracle/app/oracle/oradata/mesorcl/std_redo10.log' size 200m; alter database add standby logfile group 11 '/oracle/app/oracle/oradata/mesorcl/std_redo11.log' size 200m;
4、查看一下数据文件
SQL> select file_name from dba_data_files; /oracle/app/oracle/oradata/mesorcl/system01.dbf /oracle/app/oracle/oradata/mesorcl/sysaux01.dbf /oracle/app/oracle/oradata/mesorcl/undotbs01.dbf /oracle/app/oracle/oradata/mesorcl/users01.dbf /oracle/oradata/data01.dbf /oracle/oradata/data02.dbf
5、创建主备转换目录
根据4中的文件目录,确定所有的数据存放路径
/oracle/app/oracle/oradata/mesorcl/ /oracle/oradata/data02.dbf
6、主库创建密码并拷贝至备库(或在备库单独创建也可以)
(1)主库创建密码:
orapwd file=/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwmesorcl password=1qaz2WSX force=y;
(2)备库创建密码:
orapwd file=/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwmesorcl password=1qaz2WSX force=y;
备库改名:因为双方SID一样的,所以无需改名:PWDMESORCL.ora
7、主库参数文件配置
(1)查看配置文件位置,并备份spfile文件
show parameter spfile; create pfile from spfile;
(2)配置pfile
alter system set db_unique_name='mesorcl' scope=spfile; #此处设置db_unique_name alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(mesorcl,mesorclstd)' scope=both; #此处是启用DG功能,设置主备库的db_unique_name, alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mesorcl' scope=both; #指定重做数据在主系统和备用系统上的存储位置: #将主数据库生成的重做数据从本地联机重做日志文件归档到USE_DB_RECOVERY_FILE_DEST中的本地归档重做日志文件。 alter system set LOG_ARCHIVE_DEST_2='SERVICE=mesorclstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mesorclstd' scope=both; #这里service alter system set fal_client='mesorcl' scope=both; #tnsnames.ora中设置的本库的tnsname名称,自己作为备机时填的信息,此处client端填自己的tnsnames的名 alter system set FAL_SERVER='mesorclstd' scope=both; #本库作为备库请求远程fal_server服务端获取远程日志文件。自己作为备机时填的信息,此处Client端填自己的tnsnames的名称。 #指定FAL服务器的Oracle Net 服务名称(通常这是以主角色运行的数据库)。 #当芝加哥数据库以备用角色运行时,如果波士顿无法自动发送丢失的日志文件,它会使用波士顿数据库作为 FAL 服务器来获取(请求)丢失的归档重做日志文件。
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE; alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
#注:此处如果数据文件有多个路径,则向后增加对应路径即可如(主路径1,备路径1,主路径2,备路径2等):
alter system set DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/mesorcl/','/oracle/app/oracle/oradata/mesorcl/' scope=spfile; alter system set LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/mesorcl/','/oracle/app/oracle/oradata/mesorcl/' scope=spfile; alter system set standby_file_management='AUTO' scope=both;
重启数据库,是上述配置生效:
SQL>shutdown immediate; SQL>startup
8、主库增加如下红色listener静态监听,并重启监听
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = mesorcl) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1/) (SID_NAME = mesorcl) ) )
9、修改tnsnames.ora文件,增加备库信息MESORCLSTD
MESORCL= ( DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.33)(PORT = 1521)) (CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME = MESORCL) ) )
MESORCLSTD= ( DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.41)(PORT = 1521)) (CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME = MESORCLSTD) ) )
四、备库配置
1、启动归档
启动归档需要在mount阶段
SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open;
增大恢复区空间:
SQL>alter system set db_recovery_file_dest_size=100G;
2、查看一下数据文件
SQL> select file_name from dba_data_files; /oracle/app/oracle/oradata/mesorcl/system01.dbf /oracle/app/oracle/oradata/mesorcl/sysaux01.dbf /oracle/app/oracle/oradata/mesorcl/undotbs01.dbf /oracle/app/oracle/oradata/mesorcl/users01.dbf
3、创建主备转换目录及密码
(1)创建主备转换目录
如果主库的配置中,备库地址都是默认存在的,不创建也可。如果备库没有创建地址,则需要创建。
(2)备库创建密码
orapwd file=/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwmesorcl password=1qaz2WSX force=y;
4、备库参数文件
(1)查看配置文件位置,并备份sfpile文件
SQL>show parameter spfile;
(2)创建pfie
SQL>create pfile from spfile;
(3)通过alter直接配置spfile;
alter system set db_unique_name='mesorclstd' scope=spfile; alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(mesorclstd,mesorcl)' scope=both; 下述需要重启数据库,才能把db_unique_name生效; alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mesorclstd' scope=both; alter system set LOG_ARCHIVE_DEST_2='SERVICE=mesorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mesorcl' scope=both; alter system set fal_client='mesorclstd' scope=both; #自己作为备机时填的信息,此处client端填自己的tnsnames的名 alter system set FAL_SERVER='mesorcl' scope=both;#自己作为备机时填的信息,此处server端填对方的tnsnames的名
#注:此处如果数据文件有多个路径,则向后增加对应路径即可如: alter system set DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/mesorcl/','/oracle/app/oracle/oradata/mesorcl/' scope=spfile; alter system set LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/mesorcl/','/oracle/app/oracle/oradata/mesorcl/' scope=spfile; alter system set standby_file_management='AUTO' scope=both;
5、备库增加listener静态监听,并重启监听【此处:global_name=mesorcl 作为静态注册的服务名称】
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = mesorclstd) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1) (SID_NAME = mesorcl) ) )
6、修改tnsnames.ora文件,增加主备的tnsname
MESORCL= ( DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.33)(PORT = 1521)) (CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME = MESORCL) ) )
MESORCLSTD= ( DESCRIPTION= (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.41)(PORT = 1521)) (CONNECT_DATA= (SERVER = DEDICATED) (SERVICE_NAME = MESORCLSTD) ) )
五、主备机同步(备库)
1、测试数据库是否连通
主库:tnsping mesorcl和tnsping mesorclstd 备库:tnsping mesorcl和tnsping mesorclstd
2、利用RMAN在备库上恢复主库
(1)备库关机并开启到nomount状态
shutdown immediate startup nomount
(2)在备机登录到主库进行数据库hot copy
[oracle@std ~]$ rman target sys/1qaz2WSX@mesorcl auxiliary sys/1qaz2WSX@mesorclstd
原库:sys/1qaz2WSX@mesorcl
备库:sys/1qaz2WSX@mesorclstd
RMAN> duplicate target database for standby from active database nofilenamecheck;#【nofilenamecheck 这个参数在出现RMAN-05001再加上】
(3)同步完成,查看数据库状态
[oracle@std ~]$ sqlplus / as sysdba SYS@std> select status from v$instance;
(4)检查Dataguard 状态
select database_role,protection_mode,protection_level,open_mode from v$database; select name,open_mode,protection_mode,database_role,switchover_status from v$database;
3、启动数据库dg
(备库执行),开启后,数据开始从主库同步至备库,手动归档,数据会立即传输到备库
alter system switch logfile; alter database recover managed standby database disconnect from session; --归档方式同步(只有主库进行归档了,才会传输最新变更到备库) 或 alter database recover managed standby database using current logfile disconnect from session; --实时同步(只要主库redolog进行了变化就会变更到备库)
4、启用ADG
(1)关闭datagurade数据同步
(备库执行),关闭后,数据不再同步至备库(如果关闭了数据同步功能,即使archivelog从主库同步过来,也不会像备库表中写入数据)
alter database recover managed standby database cancel;
(2)打开数据库,再开启同步数据
alter database open; alter database recover managed standby database using current logfile disconnect from session;
(3)主库redo 日志归档到archive 日志
alter system switch logfile;(主库执行)
5、主备库检查dataguard 状态【查看archivelog序列号是否一致,下面两种都可以查看】
SQL>select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log; SQL>archive log list;【此命令在备库可以用于11g中,在12c及以上就不好用了)
6、查看日志应用情况
SYS@std> set pagesize 10; SYS@std> select sequence#,applied from v$archived_log order by sequence#;