11G单机版实施ADG

1. 前言

当数据量达到一定量后，或者不重要的业务变得重要的时候，各种防护措施就需要部署起来，比如ADG
PS：上生产，一定要先测试和验证，没问题后再上

2. 环境

3. 主库实施

3.1 查看归档模式及是否强制记录日志

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
NOARCHIVELOG NO  

若非归档模式，需要进行修改
PS：生产需要协调好停机时间,停机完相关应用后开干

3.2 检查启动参数文件（spfile目录）

PS： 若是查询值不为空，后续一些参数修改会很顺利，若是不存在，也不影响后续操作

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /mnt/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilevirdb.ora

3.3 开启归档及强制日志模式（根据3.1的查询情况确定是否需要此步骤）

若非归档非强制日志模式，需要更改并重启，若已经，若已经是，则省略此步骤

PS：若3.2发现非spfile启动，现在重启可以顺便处理了

#关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

#通过pfile创建spfile
SQL> create spfile from pfile='/mnt/pfile20240326.ora';

File created.

#启动到mount模式
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size             587204504 bytes
Database Buffers         1543503872 bytes
Redo Buffers                4923392 bytes
Database mounted.

#开启归档模式
SQL> alter database archivelog;

Database altered.

#开启强制日志
SQL> alter database force logging;

Database altered.

#更改后查看

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES

3.4 添加standby log 日志

PS：添加的组数=在线日志redo log +1 组,文件大小与在线日志组保留一致

alter database add standby logfile group 20 '/mnt/oracle/virdata/virdb/standby_redo20.log' size 300m;
alter database add standby logfile group 21 '/mnt/oracle/virdata/virdb/standby_redo21.log' size 300m;
alter database add standby logfile group 22 '/mnt/oracle/virdata/virdb/standby_redo22.log' size 300m;
alter database add standby logfile group 23 '/mnt/oracle/virdata/virdb/standby_redo23.log' size 300m;

3.4 修改初始化参数文件

下列4项，若spfile初始化参数无，修改需要重启（不增加，也不影响DG同步）

alter system set db_unique_name='virdb'   -- # 一般主库不动，为了不重启   
alter system set log_archive_max_processes=4 -- # 一般是默认就好
alter system set db_file_name_convert='/mnt/oracle/virdata','/mnt/oracle/virdata' scope=spfile ;
alter system set log_file_name_convert='/mnt/archivelog','/mnt/archivelog' scope=spfile ;

增加下列参数，在线修改

alter system set log_archive_config='dg_config=(virdb,virdb_st)' ;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_dest_1='location=/mnt/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=virdb';
alter system set log_archive_dest_2='service=virdb_st valid_for=(online_logfiles,primary_role) db_unique_name=virdb_st';
alter system set fal_client='virdb' ;
alter system set fal_server='virdb_st' ;
alter system set standby_file_management='auto' ;

PS: 为了在部署过程中，主库不会一直告警如下错误:

Tue Mar 26 20:32:08 2024
PING[ARC2]: Heartbeat failed to connect to standby 'virdb_st'. Error is 16058.

可以先设置为

# 关闭自动同步归档日志到目标端
alter system set log_archive_dest_state_2=defer;

# 开启
alter system set log_archive_dest_state_2=enable;

3.5 增加配置静态监听

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

3.6 增加配置tns（主备库一致）

virdb_pd =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = virdb)
    )
  )

virdb_st =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = virdb)
    )
  )

重新加载监听,并进行测试【监听配置须谨慎，不容有失】

lsnrctl reload
tnsping virdb_pd
tnsping virdb_st

4. 备库实施（已安装好软件）

4.1 拷贝主库密码文件及初始化参数文件到目标端

scp $ORACLE_HOME/dbs/orapwvirdb 192.168.1.11:$ORACLE_HOME/dbs/
scp /mnt/pfile20240326.ora 192.168.1.11:/mnt/pfile-dg-4H16G-ok.ora

PS:若密码文件不存在，需要生成；或忘记sys密码，需要修改，后续会用到

4.2 创建相关目录，不提前创建会报错

mkdir -p /mnt/oracle/admin/std/adump
mkdir -p /mnt/oracle/diag/rdbms/
mkdir -p /mnt/oracle/virdata/virdb
mkdir -p /mnt/oracle/flash_recovery_area
mkdir -p /mnt/archivelog

4.3 使用主库未增加配置的pfile，并进行增加备库的ADG配置

原配置

 # cat  pfile-dg-4H16G-ok.ora 

virdb.__db_cache_size=9026142208
virdb.__java_pool_size=33554432
virdb.__large_pool_size=335544320
virdb.__oracle_base='/mnt/oracle'#ORACLE_BASE set from environment
virdb.__pga_aggregate_target=2684354560
virdb.__sga_target=10737418240
virdb.__shared_io_pool_size=0
virdb.__shared_pool_size=1174405120
virdb.__streams_pool_size=100663296
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/mnt/oracle/admin/virdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/mnt/oracle/virdata/virdb/control01.ctl','/mnt/oracle/fast_recovery_area/virdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='virdb'
*.db_recovery_file_dest='/mnt/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=161061273600
*.diagnostic_dest='/mnt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=virdbXDB)'
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.log_archive_format='arch_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=2684354560
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_max_size=10737418240
*.sga_target=10737418240
*.undo_tablespace='UNDOTBS1'

新增配置

*.db_unique_name='virdb_st'
*.fal_client='virdb_st'
*.fal_server='virdb'
*.log_archive_config='dg_config=(virdb_st,virdb)'
*.log_archive_dest_1='location=/mnt/archivelog valid_for=(all_logfiles,all_roles) 
*.log_archive_dest_2='service=virdb valid_for=(online_logfiles,primary_role) 
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.db_file_name_convert='/mnt/oracle/virdata','/mnt/oracle/virdata'
*.log_file_name_convert='/mnt/archivelog','/mnt/archivelog' 
*.standby_file_management='auto'
*.log_archive_max_processes=4

4.4 增加配置静态监听

/mnt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = virdb)
      (ORACLE_HOME = /mnt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = virdb)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = virdbA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /mnt/oracle

4.5 增加配置tns（主备库一致）

virdb_pd =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = virdb)
    )
  )

virdb_st =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = virdb)
    )
  )

4.6 启动监听，并进行测试

lsnrctl start
tnsping virdb_pd
tnsping virdb_st

启动到nomount状态

create spfile from pfile='pfile-dg-4H16G-ok.ora';
startup nomount;

4.7 准备目标端初始化的脚本

# vi init_standbydb.sh

rman msglog init_standbydb.log<<EOF
connect target sys/oracle@virdb_pd
connect auxiliary sys/oracle@virdb_st
run{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
allocate auxiliary channel dg1 type disk;
allocate auxiliary channel dg2 type disk;
allocate auxiliary channel dg3 type disk;
allocate auxiliary channel dg4 type disk;
duplicate target database for standby from active database nofilenamecheck;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel dg1;
release channel dg2;
release channel dg3;
release channel dg4;
}
EOF

4.8 执行脚本开始同步数据（包括数据文件，日志文件，控制文件都会同步过来）

nohup init_standbydb.sh &

日志信息（脱敏部分，不完整）

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 26 19:57:32 2024

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

RMAN>
connected to target database: virdb (DBID=1012158571)

RMAN>
connected to auxiliary database: virdb (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=1728 device type=DISK

allocated channel: ch2
channel ch2: SID=2001 device type=DISK

allocated channel: ch3
channel ch3: SID=292 device type=DISK

allocated channel: ch4
channel ch4: SID=575 device type=DISK

allocated channel: dg1
channel dg1: SID=572 device type=DISK

allocated channel: dg2
channel dg2: SID=1141 device type=DISK

allocated channel: dg3
channel dg3: SID=1711 device type=DISK

allocated channel: dg4
channel dg4: SID=6 device type=DISK

Starting Duplicate Db at 2024:03:26 19:57:36

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/mnt/oracle/product/11.2.0/dbhome_1/dbs/orapwvirdb' auxiliary format
 '/mnt/oracle/product/11.2.0/dbhome_1/dbs/orapwvirdb'   ;
}
executing Memory Script

Starting backup at 2024:03:26 19:57:36
Finished backup at 2024:03:26 19:57:37

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/mnt/oracle/virdata/virdb/control01.ctl';
   restore clone controlfile to  '/mnt/oracle/fast_recovery_area/virdb/control02.ctl' from
 '/mnt/oracle/virdata/virdb/control01.ctl';
}
executing Memory Script

Starting backup at 2024:03:26 19:57:37
channel ch1: starting datafile copy
copying standby control file
output file name=/mnt/oracle/product/11.2.0/dbhome_1/dbs/snapcf_virdb.f tag=TAG20240326T195737 RECID=2 STAMP=1164657457
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2024:03:26 19:57:38

Starting restore at 2024:03:26 19:57:38

channel dg2: skipped, AUTOBACKUP already found
channel dg3: skipped, AUTOBACKUP already found
channel dg4: skipped, AUTOBACKUP already found
channel dg1: copied control file copy
Finished restore at 2024:03:26 19:57:46

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
 "/mnt/oracle/virdata/virdb/temp01.dbf";
   set newname for tempfile  2 to
 "/mnt/oracle/virdata/virdb/temp02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/mnt/oracle/virdata/virdb/system01.dbf";
   set newname for datafile  2 to
 "/mnt/oracle/virdata/virdb/sysaux01.dbf";
   set newname for datafile  3 to
 "/mnt/oracle/virdata/virdb/undotbs01.dbf";
   set newname for datafile  4 to
 "/mnt/oracle/virdata/virdb/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/mnt/oracle/virdata/virdb/system01.dbf"   datafile
 2 auxiliary format
 "/mnt/oracle/virdata/virdb/sysaux01.dbf"   datafile
 3 auxiliary format
 "/mnt/oracle/virdata/virdb/undotbs01.dbf"   datafile
 4 auxiliary format
 "/mnt/oracle/virdata/virdb/users01.dbf"   datafile
 
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /mnt/oracle/virdata/virdb/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME



Starting backup at 2024:03:26 19:57:52
channel ch4: starting datafile copy
input datafile file number=00002 name=/mnt/oracle/virdata/virdb/sysaux01.dbf
channel ch1: datafile copy complete, elapsed time: 00:22:32
channel ch1: starting datafile copy
input datafile file number=00003 name=/mnt/oracle/virdata/virdb/undotbs01.dbf
output file name=/mnt/oracle/virdata/virdb/undotbs01.dbf tag=TAG20240326T195752
channel ch1: datafile copy complete, elapsed time: 00:14:00

Finished backup at 2024:03:26 22:10:41

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=2 STAMP=1164665443 file name=/mnt/oracle/virdata/virdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=1164665443 file name=/mnt/oracle/virdata/virdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=1164665443 file name=/mnt/oracle/virdata/virdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=1164665443 file name=/mnt/oracle/virdata/virdb/users01.dbf
Finished Duplicate Db at 2024:03:26 22:10:51

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

released channel: dg1

released channel: dg2

released channel: dg3

released channel: dg4

RMAN>

Recovery Manager complete.

4.9 查询并启动数据库【当前出于mounted模式】

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

OPEN_MODE	LOG_MODE     FOR
-----------	------------ ---
MOUNTED   	ARCHIVELOG   YES

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

4.10 开启试试同步

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

4.11 查看备库是否在应用日志进行恢复

PS：也可以看看主库的变更，在备库有没有变更

SQL> select process,client_process,sequence#,status from v$managed_standby;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH         215097 CLOSING
ARCH      ARCH              0 CONNECTED
ARCH      ARCH         215098 CLOSING
ARCH      ARCH         215099 CLOSING
RFS       UNKNOWN           0 IDLE
RFS       ARCH              0 IDLE
RFS       UNKNOWN           0 IDLE
RFS       LGWR         215100 IDLE
MRP0      N/A          215100 WAIT_FOR_LOG

9 rows selected.

4.12 优化参数

SQL> show parameter parallel_execution_message_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size      integer     16384
SQL> alter system set parallel_execution_message_size=32768 scope=spfile;

System altered.

SQL>
SQL> alter system set filesystemio_options=setall scope=spfile;

System altered.