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

老苏畅谈运维

已于 2024-08-13 20:26:07 修改

阅读量541

点赞数 4

分类专栏： dataguard oracle 数据迁移文章标签： oracle 数据库 dataguard

于 2024-08-12 15:47:09 首次发布

文章来源于老苏畅谈运维，欢迎您转载，转载声明来源老苏畅谈运维

本文链接：https://blog.csdn.net/zhirongsu/article/details/141133299

版权

oracle 同时被 3 个专栏收录

40 篇文章 2 订阅

订阅专栏

dataguard

9 篇文章 0 订阅

订阅专栏

数据迁移

5 篇文章 0 订阅

订阅专栏

上一篇文章介绍了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

关注我，学习更多的数据库知识！
请添加图片描述