Oracle搭建一主两备dataguard环境的详细步骤

原创 szrsu 2024-08-12

1519

上一篇文章介绍了Oracle一对多(一主多备)的DG环境如何进行switchover切换，也许你会问Oracle一主两备dataguard环境要怎么搭建，本篇文章将为你讲述搭建一主两备dataguard环境的详细步骤。

环境说明

主机名	IP地址	db_unique_name	数据库角色
ora11g	10.10.10.160	ora11g	主库
ora11gdg	10.10.10.41	dgora11g	备库1
ora11gdg2	10.10.10.47	sbora11g	备库2

说明：10.10.10.160为主库，10.10.10.41和10.10.10.47都是它的备库。

主库配置

主库打开归档

–确认数据库开启归档，如未开启归档，请开启归档。

sys@ora11g> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /u01/oradata/archivelog
Oldest online log sequence     307
Next log sequence to archive   313
Current log sequence	       313

--如未开启归档，请打开
startup mount;
alter system set log_archive_dest_1='location=/u01/oradata/archivelog' scope=both;
alter database archivelog;
alter database open;
复制

主库打开数据强制日志

--开启强制日志
alter database force logging;
--查询实例的归档模式和附加日志
sys@ora11g> select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES
复制

添加standby log

standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。

--主库查出大小
select  group#,thread#,bytes/1024/1024 M from  v$log;

--添加standby log
alter database add standby logfile thread 1 group 4 '/u01/oradata/ora11g/stdredo01.log' size 50M; 
alter database add standby logfile thread 1 group 5 '/u01/oradata/ora11g/stdredo02.log' size 50M;
alter database add standby logfile thread 1 group 6 '/u01/oradata/ora11g/stdredo03.log' size 50M; 
alter database add standby logfile thread 1 group 7 '/u01/oradata/ora11g/stdredo04.log' size 50M; 

sys@ora11g> select group#,thread#,sequence#,archived,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
	 4	    1	       0 YES UNASSIGNED
	 5	    1	       0 YES UNASSIGNED
	 6	    1	       0 YES UNASSIGNED
	 7	    1	       0 YES UNASSIGNED


复制

主库配置tnsnames.ora

主库：
vi tnsnames.ora
ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.160)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )

DGORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.41)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgora11g)
    )
  )

SBORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.47)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sbora11g)
    )
  )

复制

主库配置DG相关参数

alter system set log_archive_config='DG_CONFIG=(ora11g,dgora11g,sbora11g)' scope=both sid='*';
alter system set log_archive_dest_1='location=/u01/oradata/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ora11g' scope=both sid='*';
alter system set log_archive_dest_2='SERVICE=dgora11g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dgora11g' scope=both sid='*';
alter system set log_archive_dest_3='SERVICE=sbora11g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=sbora11g' scope=both sid='*';
alter system set fal_server='dgora11g','sbora11g' scope=both sid='*';
alter system set fal_client='ora11g' scope=both sid='*';
alter system set standby_file_management=auto scope=both sid='*';
--alter system set log_archive_dest_state_2=defer scope=both sid='*';
alter system set log_archive_dest_state_2=enable scope=both sid='*';
--alter system set log_archive_dest_state_3=defer scope=both sid='*';
alter system set log_archive_dest_state_3=enable scope=both sid='*';
alter system set db_file_name_convert='/u01/oradata/dgora11g','/u01/oradata/ora11g','/u01/oradata/sbora11g','/u01/oradata/ora11g' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/oradata/dgora11g','/u01/oradata/ora11g','/u01/oradata/sbora11g','/u01/oradata/ora11g' scope=spfile sid='*';

#db_file_name_convert、log_file_name_convert需要重启数据库才能生效，若生产不能马上重启，需要找停机窗口重启，不影响后续的dg同步。

#####查看修改是否生效
set linesize 500 pages 1000
col value for a100
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_3',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'log_archive_dest_state_3',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server',
'fal_client',
'db_file_name_convert',
'log_file_name_convert',
'standby_file_management')
/


NAME						   VALUE
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
db_file_name_convert				   /u01/oradata/dgora11g, /u01/oradata/ora11g, /u01/oradata/sbora11g, /u01/oradata/ora11g
log_file_name_convert				   /u01/oradata/dgora11g, /u01/oradata/ora11g, /u01/oradata/sbora11g, /u01/oradata/ora11g
log_archive_dest_1				   location=/u01/oradata/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=ora11g
log_archive_dest_2				   SERVICE=dgora11g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dgora11g
log_archive_dest_3				   SERVICE=sbora11g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=sbora11g
log_archive_dest_state_1			   ENABLE
log_archive_dest_state_2			   ENABLE
log_archive_dest_state_3			   ENABLE
fal_client					   ora11g
fal_server					   dgora11g, sbora11g
log_archive_config				   DG_CONFIG=(ora11g,dgora11g,sbora11g)
log_archive_format				   %t_%s_%r.dbf
log_archive_max_processes			   4
standby_file_management 			   AUTO
remote_login_passwordfile			   EXCLUSIVE
db_name 					   ora11g
db_unique_name					   ora11g

17 rows selected.

复制

拷贝主库的pfile文件和密码文件到备库

创建主库pfil文件，并将pfile文件和密码文件复制到备1和备2

create pfile='/home/oracle/initbak0808.bak' from spfile;

$ scp orapwora11g 10.10.10.41:/u01/app/oracle/product/11.2.0/db/dbs/orapwdgora11g
$ scp orapwora11g 10.10.10.47:/u01/app/oracle/product/11.2.0/db/dbs/orapwsbora11g
复制

备库1配置

第一个备库（10.10.10.41）

静态监听配置

$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER=
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dgora11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = dgora11g)
    )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gdg)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

复制

启动监听

$ lsnrctl start
$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-AUG-2024 14:57:07

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                12-AUG-2024 10:49:21
Uptime                    0 days 4 hr. 7 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora11gdg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11gdg)(PORT=1521)))
Services Summary...
Service "dgora11g" has 2 instance(s).
  Instance "dgora11g", status UNKNOWN, has 1 handler(s) for this service...
  Instance "dgora11g", status READY, has 1 handler(s) for this service...
Service "ora11g" has 1 instance(s).
  Instance "dgora11g", status READY, has 1 handler(s) for this service...
Service "ora11gXDB" has 1 instance(s).
  Instance "dgora11g", status READY, has 1 handler(s) for this service...
The command completed successfully

复制

tnsnames.ora配置

$ cat tnsnames.ora 
ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.160)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )

DGORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.41)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgora11g)
    )
  )
  
SBORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.47)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sbora11g)
    )
  )

复制

修改pfile

$ cat ora11gdg.txt 
*.audit_file_dest='/u01/app/oracle/admin/dgora11g/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='/u01/oradata/dgora11g/control01.ctl','/u01/app/oracle/fast_recovery_area/dgora11g/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/oradata/ora11g','/u01/oradata/dgora11g'
*.db_files=5000
*.db_name='ora11g'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_unique_name='dgora11g'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.fal_client='dgora11g'
*.fal_server='ora11g'
*.log_archive_config='DG_CONFIG=(ora11g,dgora11g)'
*.log_archive_dest_1='location=/u01/oradata/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=dgora11g'
*.log_archive_dest_2='SERVICE=ora11g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ora11g'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/oradata/ora11g','/u01/oradata/dgora11g'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=924844032
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.service_names='ora11g'
*.session_cached_cursors=300
*.sga_max_size=3700424704
*.sga_target=3700424704
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

##主要修改DG相关的参数，及添加db_unique_name、service_names
复制

启动到nomount状态

--创建相关目录
[oracle@ora11gdg:~]$ mkdir -p /u01/app/oracle/admin/dgora11g/adump
[oracle@ora11gdg:~]$ mkdir -p /u01/oradata/dgora11g
[oracle@ora11gdg:~]$ mkdir -p /u01/app/oracle/fast_recovery_area/dgora11g
[oracle@ora11gdg:~]$ mkdir -p /u01/oradata/dgora11g
[oracle@ora11gdg:~]$ mkdir -p /u01/oradata/archivelog

--启动到nomount状态
[oracle@ora11gdg:~]$ export ORACLE_SID=dgora11g
[oracle@ora11gdg:~]$ env|grep SID
ORACLE_SID=dgora11g
[oracle@ora11gdg:~]$ sqlplus / as sysdba
SQL> startup nomount pfile='/home/oracle/ora11gdg.txt';
ORACLE instance started.

Total System Global Area 3691200512 bytes
Fixed Size		    2258680 bytes
Variable Size		 1392511240 bytes
Database Buffers	 2281701376 bytes
Redo Buffers		   14729216 bytes
SQL> create spfile from pfile='/home/oracle/ora11gdg.txt';
复制

rman duplicate创建备库

rman target sys/oracle@ORA11G auxiliary sys/oracle@DGORA11G
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

需要限速时，用以下命令：
run {
allocate channel c1 type disk rate 200M;
allocate channel c2 type disk rate 200M;
allocate channel c3 type disk rate 200M;
allocate auxiliary channel st1 type disk rate 200M;
allocate auxiliary channel st2 type disk rate 200M;
allocate auxiliary channel st3 type disk rate 200M;
duplicate target database for standby from active database nofilenamecheck dorecover;
release channel c1;
release channel c2;
release channel c3;
release channel st1;
release channel st2;
release channel st3;
}
复制

开启同步

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
MOUNTED

SQL> alter database open;

Database altered.


SQL> select log_mode,open_mode ,database_role from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
--------------- --------------- --------------------
ARCHIVELOG READ ONLY PHYSICAL STANDBY

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

sys@dgora11g> select log_mode,open_mode ,database_role from v$database;

LOG_MODE     OPEN_MODE		  DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG   READ ONLY WITH APPLY PHYSICAL STANDBY

--查看同步情况
col error for a50;
set linesize 400;
select status,error,gap_status from v$archive_dest_status;

select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;

set linesize 400;
select 'thread:' || a.thread# || ',primary sequence:' || b.max_available || ',primary sequence:' ||
       a.max_applied , b.max_available - a.max_applied log_gap 
  from (select thread#, max(sequence#) max_applied
          from gv$archived_log
         where applied = 'YES'
         group by thread#) a,
       (select thread#, max(sequence#) max_available
          from gv$archived_log
         group by thread#) b
 where a.thread# = b.thread#;
复制

备库2配置

第二个备库（10.10.10.47）

静态监听配置

$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER=
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sbora11g)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = sbora11g)
    )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora11gdg2)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

复制

启动监听

$ lsnrctl start
$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-AUG-2024 16:54:52

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-AUG-2024 16:37:43
Uptime                    0 days 0 hr. 17 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora11gdg2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora11gdg2)(PORT=1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "sbora11g", status BLOCKED, has 1 handler(s) for this service...
Service "sbora11g" has 2 instance(s).
  Instance "sbora11g", status UNKNOWN, has 1 handler(s) for this service...
  Instance "sbora11g", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
复制

tnsnames.ora 配置

$ cat tnsnames.ora 
ORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.160)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )

DGORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.41)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dgora11g)
    )
  )
  
SBORA11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.47)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sbora11g)
    )
  )

复制

修改pfile

$ cat sbora11g.txt 
*.audit_file_dest='/u01/app/oracle/admin/sbora11g/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='/u01/oradata/sbora11g/control01.ctl','/u01/app/oracle/fast_recovery_area/sbora11g/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/oradata/ora11g','/u01/oradata/sbora11g'
*.db_files=5000
*.db_name='ora11g'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_unique_name='sbora11g'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora11gXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.fal_client='sbora11g'
*.fal_server='ora11g'
*.log_archive_config='DG_CONFIG=(ora11g,sbora11g)'
*.log_archive_dest_1='location=/u01/oradata/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=sbora11g'
*.log_archive_dest_2='SERVICE=ora11g LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ora11g'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/oradata/ora11g','/u01/oradata/sbora11g'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=924844032
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.service_names='ora11g'
*.session_cached_cursors=300
*.sga_max_size=3700424704
*.sga_target=3700424704
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

##主要修改DG相关的参数，及添加db_unique_name、service_names
复制

启动到nomount状态

--创建相关目录
[oracle@ora11gdg2:~]$ mkdir -p /u01/app/oracle/admin/sbora11g/adump
[oracle@ora11gdg2:~]$ mkdir -p /u01/oradata/sbora11g
[oracle@ora11gdg2:~]$ mkdir -p /u01/app/oracle/fast_recovery_area/sbora11g
[oracle@ora11gdg2:~]$ mkdir -p /u01/oradata/sbora11g
[oracle@ora11gdg2:~]$ mkdir -p /u01/oradata/archivelog

--启动到nomount状态
[oracle@ora11gdg2:~]$ export ORACLE_SID=sbora11g
[oracle@ora11gdg2:~]$ env|grep SID
ORACLE_SID=sbora11g
[oracle@ora11gdg2:~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 8 16:51:37 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/sbora11g.txt';
ORACLE instance started.

Total System Global Area 3691200512 bytes
Fixed Size		    2258680 bytes
Variable Size		 1392511240 bytes
Database Buffers	 2281701376 bytes
Redo Buffers		   14729216 bytes
SQL> create spfile from pfile='/home/oracle/sbora11g.txt';
复制

rman duplicate创建备库

$ rman target sys/oracle@ORA11G auxiliary sys/oracle@SBORA11G

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 8 16:58:17 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA11G (DBID=260591189)
connected to auxiliary database: ORA11G (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

需要限速时，用以下命令：
run {
allocate channel c1 type disk rate 200M;
allocate channel c2 type disk rate 200M;
allocate channel c3 type disk rate 200M;
allocate auxiliary channel st1 type disk rate 200M;
allocate auxiliary channel st2 type disk rate 200M;
allocate auxiliary channel st3 type disk rate 200M;
duplicate target database for standby from active database nofilenamecheck dorecover;
release channel c1;
release channel c2;
release channel c3;
release channel st1;
release channel st2;
release channel st3;
}
复制

开启同步

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
MOUNTED

SQL> alter database open;

Database altered.


SQL> select log_mode,open_mode ,database_role from v$database;

LOG_MODE OPEN_MODE DATABASE_ROLE
--------------- --------------- --------------------
ARCHIVELOG READ ONLY PHYSICAL STANDBY

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

sys@sbora11g > select log_mode,open_mode ,database_role from v$database;

LOG_MODE     OPEN_MODE		  DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG   READ ONLY WITH APPLY PHYSICAL STANDBY

--查看同步情况
col error for a50;
set linesize 400;
select status,error,gap_status from v$archive_dest_status;

select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;

set linesize 400;
select 'thread:' || a.thread# || ',primary sequence:' || b.max_available || ',primary sequence:' ||
       a.max_applied , b.max_available - a.max_applied log_gap 
  from (select thread#, max(sequence#) max_applied
          from gv$archived_log
         where applied = 'YES'
         group by thread#) a,
       (select thread#, max(sequence#) max_available
          from gv$archived_log
         group by thread#) b
 where a.thread# = b.thread#;
复制

同步测试

–主库创建表插入测试数据

sys@sbora11g > create table tt0812 as select * from dba_objects;

Table created.
复制

–两备库查询

sys@dgora11g> select count(*) from tt0812;  

  COUNT(*)
----------
     86269
 sys@sbora11g >  select count(*) from tt0812;

  COUNT(*)
----------
     86269
  
复制

至此主从切换已完成，新主库应与两从库正常同步。

后续工作

–创建数据库启动服务

# cat /etc/oratab
# cat /etc/init.d/oracle 
#!/bin/bash
#
#################FUNCTION#############
#
# AutoStart Oracle and listener Using dbstart
# AutoStop Oracle and listener Using dbstop
#
#####################################
#
#chkconfig: 345 99 10
#description: script for the Oracle Instance, Listener 


source /home/oracle/.bash_profile
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
LOG=/var/log/oracle.log


case "$1" in
start)
echo "Starting Oracle Databases ... "
echo "-------------------------------------------------" >> $LOG 2>&1
date +" %T %a %D : Starting Oracle Databasee as part of system up." >> $LOG 2>&1
su - oracle -c "$ORACLE_HOME/bin/dbstart" >> $LOG 2>&1
echo "Done."
date +" %T %a %D : Finished." >> $LOG 2>&1
echo "-------------------------------------------------" >> $LOG 2>&1
touch /var/lock/subsys/oracle
echo "Recovering Oracle Databases ... "
su - oracle -c 'sqlplus -s / as sysdba <<EOF
   whenever sqlerror exit sql.sqlcode;
   set echo off
   set heading off
   host echo "Database open mode is:" 
   select open_mode from v\$database;
   host echo "Recovering Oracle Databases......"
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
   host echo "Database open mode is:"
   select open_mode from v\$database;


   exit;
EOF
'
;;
stop)
echo "Stopping Oracle Databases ... "
echo "-------------------------------------------------" >> $LOG 2>&1
date +" %T %a %D : Stopping Oracle Databases as part of system down." >> $LOG 2>&1
su - oracle -c "$ORACLE_HOME/bin/dbshut" >> $LOG 2>&1
echo "Done."
date +" %T %a %D : Finished." >> $LOG 2>&1
echo "-------------------------------------------------" >> $LOG 2>&1
rm -f /var/lock/subsys/oracle


;;
restart)
$0 stop
$0 start


;;
*)
echo "Usage: oracle {start|stop|restart}"
exit 1
esac

# chmod +x /etc/init.d/oracle
# chkconfig --add oracle
复制

cat /etc/oratab

sbora11g:/u01/app/oracle/product/11.2.0/db:Y

cat /etc/init.d/oracle 

#!/bin/bash
#
#################FUNCTION#############
#

AutoStart Oracle and listener Using dbstart

AutoStop Oracle and listener Using dbstop

#
#####################################

#
#chkconfig: 345 99 10
#description: script for the Oracle Instance, Listener 


source /home/oracle/.bash_profile
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
LOG=/var/log/oracle.log


case "$1" in
start)
echo "Starting Oracle Databases ... "
echo "-------------------------------------------------" >> $LOG 2>&1
date +" %T %a %D : Starting Oracle Databasee as part of system up." >> $LOG 2>&1
su - oracle -c "$ORACLE_HOME/bin/dbstart" >> $LOG 2>&1
echo "Done."
date +" %T %a %D : Finished." >> $LOG 2>&1
echo "-------------------------------------------------" >> $LOG 2>&1
touch /var/lock/subsys/oracle
echo "Recovering Oracle Databases ... "
su - oracle -c 'sqlplus -s / as sysdba <<EOF
   whenever sqlerror exit sql.sqlcode;
   set echo off
   set heading off
   host echo "Database open mode is:" 
   select open_mode from v\$database;
   host echo "Recovering Oracle Databases......"
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
   host echo "Database open mode is:"
   select open_mode from v\$database;


   exit;
EOF
'
;;
stop)
echo "Stopping Oracle Databases ... "
echo "-------------------------------------------------" >> $LOG 2>&1
date +" %T %a %D : Stopping Oracle Databases as part of system down." >> $LOG 2>&1
su - oracle -c "$ORACLE_HOME/bin/dbshut" >> $LOG 2>&1
echo "Done."
date +" %T %a %D : Finished." >> $LOG 2>&1
echo "-------------------------------------------------" >> $LOG 2>&1
rm -f /var/lock/subsys/oracle


;;
restart)
$0 stop
$0 start

;;
*)
echo "Usage: oracle {start|stop|restart}"
exit 1
esac

# chmod +x /etc/init.d/oracle
# chkconfig --add oracle
复制

–备库配置归档删除脚本

$ cat /home/oracle/scripts/del_arch.sh 
#!/bin/bash
source ~/.bash_profile
deltime=$(date +"20%y%m%d%H%M%S")
rman target / nocatalog msglog /home/oracle/scripts/del_arch_$deltime.log <<-EOF
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-7';
delete noprompt force archivelog until time 'SYSDATE-10';
EOF
复制

最后修改时间：2024-08-13 20:28:28

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

星星之火

6月前

人生最幸福的事，不是活得像别人，而是在努力之后，活得更像自己。所以，只要还有努力的机会，就不要轻易说放弃。

6月前

cqiwen

7月前

有个疑问，文中以下查询的数量不一致，是什么原因呢？ sys@dgora11g> select count(*) from tt0812; COUNT(*) ---------- 86266 sys@sbora11g > select count(*) from tt0812; COUNT(*) ---------- 86269

7月前

szrsu

7月前

抱歉，测试了两次，忘了更新，都是86269，已更正，感谢指出。

7月前

Oracle搭建一主两备dataguard环境的详细步骤

环境说明

主库配置

主库打开归档

主库打开数据强制日志

添加standby log

主库配置tnsnames.ora

主库配置DG相关参数

拷贝主库的pfile文件和密码文件到备库

备库1配置

静态监听配置

tnsnames.ora配置

修改pfile

启动到nomount状态

rman duplicate创建备库

开启同步

备库2配置

静态监听配置

tnsnames.ora 配置

修改pfile

启动到nomount状态

rman duplicate创建备库

开启同步

同步测试

后续工作

评论

相关阅读