Oracle 11g 搭建DG（ADG方式）

转载

mob604756fc3573 2021-07-15 16:15:00

文章标签 灾备 adg搭建操作指南 oracle11g oracle sql 文章分类 代码人生

centos7 Oracle11g ADG搭建操作说明

1.准备工作

系统版本： Red Hat Enterprise Linux 6.5（64位）

软件版本：Oracle Database 11g Release 2 (11.2.0.4)

首先得准备两个能够PING通，并且装了Oracle软件且已经建库的Linux虚拟机。操作系统不限，至少有一个虚拟机已经建好库，最好两个都建好相同SID的库，这样少很多创建目录的麻烦，这里库的SID都是blockOra，Linux严格区分大小写，所以SID的大小写得注意。

我这里有两个名为dgmaster和standby的Linux虚拟机

172.16.7.193 dgmaster（主库）

172.16.7.194 standby （备库）

2.在主库进行操作

2.1强制force logging

修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
Database mounted.
SQL> alter database force logging; 
 
Database altered.

2.2开启主库的归档模式

修改数据库为归档模式，因为dg是通过传送归档日志到备库然后应用来保证主备库一致的。

SQL> alter database archivelog; 
 
Database altered.

2.3创建standby redo log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oradata/blockOra/stredo05.log') size 200M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oradata/blockOra/stredo06.log') size 200M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/oradata/blockOra/stredo07.log') size 200M; 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u01/oradata/blockOra/stredo08.log') size 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u01/oradata/blockOra/stredo09.log') size 200M;

standby redo log多少组才合理

单机情况下
所有redo_log组数+1

RAC环境下
所有redo log组数+实例数
正常情况下，一般每个实例的redo log组数目是一样的，比如为你，则standbby redo log组数为(n+1)*thread

假如有个rac共三个实例，每个实例都是3个log组，那么如果要做dg的standby log要增加12个standby loggroup
(3+1)*3=12

假如有个rac共三个实例，实例1有3个log组，实例2有4个log组，实例3有5个log组，总共有12个log组，那么如果要做dg的standby log要增加15个standby loggroup
所有redo log组数+实例数=(3+4+5)+3=15

2.4创建pfile

这里创建pfile是为了做一些主库参数的配置，并且还得拷贝到备库再次修改成备库的配置。

SQL> create pfile from spfile;
 
File created.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2.5创建主库归档目录

建立这个目录是为了存放主库的归档日志文件，并且这个目录会和其他数据文件等等一起拷贝到备库。

[oracle@dgmaster blockOra]$ mkdir archivelog 
[oracle@dgmaster blockOra]$ cd archivelog/
[oracle@dgmaster archivelog]$ ls
[oracle@dgmaster archivelog]$ pwd
/u01/app/oracle/oradata/blockOra/archivelog

2.6在主备库同时创建静态监听listener和tnsname

主库listener.ora

[oracle@dgmaster ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.7.193)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = blockOra)
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  (SID_NAME = blockOra)
  )
)

ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dgmaster ~]$

备库listener.ora

[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.7.194)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = blockOra)
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  (SID_NAME = blockOra)
  )
)

ADR_BASE_LISTENER = /u01/app/oracle
[oracle@standby ~]$

主库、备库tnsnames.ora

[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BLOCKORA_PD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.7.193)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = blockOra)
    )
  )


BLOCKORA_ST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.7.194)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = blockOra)
    )
  )

[oracle@standby ~]$

2.7修改主库pfile文件

[oracle@dgmaster ~]$ cat /u01/app/oracle/product/11.2.0/db_1/dbs/initblockOra.ora
blockOra.__db_cache_size=1593835520
blockOra.__java_pool_size=16777216
blockOra.__large_pool_size=16777216
blockOra.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
blockOra.__pga_aggregate_target=1459617792
blockOra.__sga_target=2164260864
blockOra.__shared_io_pool_size=0
blockOra.__shared_pool_size=469762048
blockOra.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/blockOra/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/blockOra/control01.ctl','/u01/app/oracle/flash_recovery_area/blockOra/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='blockOra'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=blockOraXDB)'
*.memory_target=3613392896
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=blockOra_pd
LOG_ARCHIVE_CONFIG='DG_CONFIG=(blockOra_pd,blockOra_st)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/archivelog/blockOra/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=blockOra_pd'
LOG_ARCHIVE_DEST_2=
'SERVICE=blockOra_st ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=blockOra_st'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=blockOra_st
STANDBY_FILE_MANAGEMENT=AUTO
[oracle@dgmaster ~]$

2.8用pfile启动主库，并创建spfile

SQL> shutdown immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initblockOra.ora'
ORACLE instance started.
 
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
SQL> create spfile from pfile;
 
File created.

2.9将口令验证文件和pfile发送到备库

[oracle@dgmaster dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@dgmaster dbs]$ scp orapwblockOra 192.168.56.43:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
 [oracle@dgmaster dbs]$ scp initblockOra.ora 192.168.56.43:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

3.在备库做一些操作

3.1在备库修改从主库拷贝来的pfile

[oracle@standby ~]$ cat /u01/app/oracle/product/11.2.0/db_1/dbs/initblockOra.ora
blockOra.__db_cache_size=1593835520
blockOra.__java_pool_size=16777216
blockOra.__large_pool_size=16777216
blockOra.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
blockOra.__pga_aggregate_target=1459617792
blockOra.__sga_target=2164260864
blockOra.__shared_io_pool_size=0
blockOra.__shared_pool_size=469762048
blockOra.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/blockOra/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/blockOra/control01.ctl','/u01/app/oracle/flash_recovery_area/blockOra/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='blockOra'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=blockOraXDB)'
*.memory_target=3613392896
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=blockOra_st
LOG_ARCHIVE_CONFIG='DG_CONFIG=(blockOra_pd,blockOra_st)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/archivelog/blockOra
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=blockOra_st'
LOG_ARCHIVE_DEST_2=
'SERVICE=blockOra_pd ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=blockOra_pd'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=blockOra_pd
STANDBY_FILE_MANAGEMENT=AUTO
[oracle@standby ~]$

3.2启动备库到nomount

[oracle@standbydbs]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 16:52:07 2016
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area 1653518336 bytes
Fixed Size            2253784 bytes
Variable Size         1006636072 bytes
Database Buffers      637534208 bytes
Redo Buffers            7094272 bytes
SQL>

3.3开始使用RMAN进行ADG

确保sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (NONE) 不要ALL

[oracle@standby dbs]$ rman target sys/sp851531@blockOra_pd auxiliary sys/sys@blockOra_st
 
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 23 17:26:33 2016
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ORCL (DBID=1434698509)
connected to auxiliary database: ORCL (not mounted)
 
RMAN> duplicate target database for standby from active database nofilenamecheck;
 
Starting Duplicate Db at 23-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
 
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwblockOra' auxiliary format 
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwblockOra'   ;
}
executing Memory Script
 
Starting backup at 23-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
Finished backup at 23-MAR-16
 
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/blockOra/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/blockOra/control02.ctl' from 
 '/u01/app/oracle/oradata/blockOra/control01.ctl';
}
executing Memory Script
 
Starting backup at 23-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_blockOra.f tag=TAG20160323T172644 RECID=4 STAMP=907262805
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-MAR-16
 
Starting restore at 23-MAR-16
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-MAR-16
 
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
 
sql statement: alter database mount standby database
 
contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/blockOra/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/blockOra/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/blockOra/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/blockOra/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/blockOra/users01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/blockOra/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oracle/oradata/blockOra/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/app/oracle/oradata/blockOra/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/app/oracle/oradata/blockOra/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/app/oracle/oradata/blockOra/users01.dbf"   datafile 
 5 auxiliary format 
 "/u01/app/oracle/oradata/blockOra/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/blockOra/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 23-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/blockOra/system01.dbf
output file name=/u01/app/oracle/oradata/blockOra/system01.dbf tag=TAG20160323T172653
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/blockOra/sysaux01.dbf
output file name=/u01/app/oracle/oradata/blockOra/sysaux01.dbf tag=TAG20160323T172653
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/blockOra/example01.dbf
output file name=/u01/app/oracle/oradata/blockOra/example01.dbf tag=TAG20160323T172653
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/blockOra/undotbs01.dbf
output file name=/u01/app/oracle/oradata/blockOra/undotbs01.dbf tag=TAG20160323T172653
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/blockOra/users01.dbf
output file name=/u01/app/oracle/oradata/blockOra/users01.dbf tag=TAG20160323T172653
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-MAR-16
sql statement: alter system archive log current
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=907262888 file name=/u01/app/oracle/oradata/blockOra/example01.dbf
Finished Duplicate Db at 23-MAR-16
RMAN>

3.3打开备库并开启apply service

[oracle@standby dbs]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 23 17:48:47 2016
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter database open;
 
Database altered.
 
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ ONLY
 
SQL> alter database recover managed standby database disconnect from session;
 
Database altered.

以上需要切换日志后才能同步数据，如果需要实时同步，备库执行下面操作

alter database recover managed standby database cancel;

alter database recover managed standby database using current logfile disconnect;