ORACLE单实例ADG搭建

1.必要配置

• 1.1 查看是否开启归档

SYS@PROD5 >startup mount;
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size		    2923824 bytes
Variable Size		  838861520 bytes
Database Buffers	  369098752 bytes
Redo Buffers		   13852672 bytes
Database mounted.

SYS@PROD5 >archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Next log sequence to archive   26
Current log sequence	       26

我当前是已经开启归档的，如果没有开启归档，需要开启归档

SYS@PROD5 >alter database archivelog;

Database altered.

• 1.2 启库开启强制日志

SYS@PROD5 >alter database open;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING

• 1.3 添加standby logfile

这里要根据实际的情况添加一般比redo多一组

SYS@PROD5 >select group#,bytes/1024/1024 from v$log;

    GROUP# BYTES/1024/1024
---------- ---------------
	 1		50
	 2		50
	 3		50

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog3.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog4.rdo') SIZE 50M;

2.修改主库参数

• 2.1 使用当前的spfile创建pfile

SYS@PROD5 >create pfile='/home/oracle/pfile.ora' from spfile

• 2.2 修改pfile

[oracle@host01 ~]$ vi pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/PROD5/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/PROD5/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD5/control02.ctl'
*.db_block_size=8192
*.db_domain='example.com'
*.db_name='PROD5'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.memory_target=1160m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=PROD5H1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD5H1,PROD5H2)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=PROD5H1'
LOG_ARCHIVE_DEST_2=
 'SERVICE=PROD5H2 ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=PROD5H2'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=PROD5H2
DB_FILE_NAME_CONVERT='/PROD5H2/','/PROD5H1/'
LOG_FILE_NAME_CONVERT='/PROD5H2/','/PROD5H1/'
STANDBY_FILE_MANAGEMENT=AUTO




注：可以自行通过官方文档提供的基本参数进行修改
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=boston'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='/boston/','/chicago/'
LOG_FILE_NAME_CONVERT='/boston/','/chicago/' 
STANDBY_FILE_MANAGEMENT=AUTO

• 2.3 使用新修改的pfile创建spfile后重启库

SYS@PROD5 >create spfile from pfile='/home/oracle/pfile.ora';
SYS@PROD5 >startup

• 2.4 验证

SYS@PROD5 >show parameter name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name		     string
db_file_name_convert		     string	 /PROD5H2/, /PROD5H1/
db_name 			     string	 PROD5
db_unique_name			     string	 PROD5H1
global_names			     boolean	 FALSE
instance_name			     string	 PROD5
lock_name_space 		     string
log_file_name_convert		     string	 /PROD5H2/, /PROD5H1/
pdb_file_name_convert		     string
processor_group_name		     string

SYS@PROD5 >show parameter log_archive_dest_

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1		     string	 LOCATION=USE_DB_RECOVERY_FILE_
						 DEST
						   VALID_FOR=(ALL_LOGFILES,ALL_
						 ROLES)
						   DB_UNIQUE_NAME=PROD5H1

log_archive_dest_2		     string	 SERVICE=PROD5H2 ASYNC
						   VALID_FOR=(ONLINE_LOGFILES,P
						 RIMARY_ROLE)
						   DB_UNIQUE_NAME=PROD5H2

3.添加tnsname与监听文件

• 3.1 添加tns（主备库都操作）

PROD5H1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host01.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD5H1.example.com)
    )
  )
PROD5H2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD5H2.example.com)
    )
  )

• 3.2 添加监听（主备库都操作）

主：
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=PROD5H1.example.com)
          (SID_NAME=PROD5)
          (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
         )
        )

备：
SID_LIST_LISTENER=
   (SID_LIST=
       (SID_DESC=
          (GLOBAL_DBNAME=PROD5H2.example.com)
          (SID_NAME=PROD5)
          (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
         )
        )

• 3.3 重启监听（主备库都操作）

[oracle@host01 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-AUG-2022 17:00:47

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

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.example.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                24-AUG-2022 17:00:47
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.example.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PROD5H1.example.com" has 1 instance(s).
  Instance "PROD5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4.修改传输密码文件,pfile至备库

5.修改备库pfile参数

• 5.1 修改备库pfile

[oracle@host02 ~]$ cat /home/oracle/pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/PROD5/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/PROD5/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD5/control02.ctl'
*.db_block_size=8192
*.db_domain='example.com'
*.db_name='PROD5'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.memory_target=1160m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=PROD5H2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD5H2,PROD5H1)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=USE_DB_RECOVERY_FILE_DEST 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=PROD5H2'
LOG_ARCHIVE_DEST_2=
 'SERVICE=PROD5H1 ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=PROD5H1'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=PROD5H1
DB_FILE_NAME_CONVERT='/PROD5H1/','/PROD5H2/'
LOG_FILE_NAME_CONVERT='/PROD5H1/','/PROD5H2/' 
STANDBY_FILE_MANAGEMENT=AUTO

• 5.2 创建spfile后重启库至nomount状态

SYS@PROD5 >create spfile from pfile='/home/oracle/pfile.ora';
SYS@PROD5 >startup nomount;

6.使用duplicate复制库

主库操作：
[oracle@host01 admin]$ rman target sys/oracle@prod5h1 auxiliary sys/oracle@prod5h2

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 24 17:09:37 2022

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

connected to target database: PROD5 (DBID=1606298904)
connected to auxiliary database: PROD5 (not mounted)

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

完成后备库应该是处于mount状态

7.开启备库日志应用

SYS@PROD5   >alter database recover managed standby database using current logfile disconnect;
Database altered.

8.查看ADG状态

SYS@PROD5   >select inst_id,PROCESS,status from gv$managed_standby;

   INST_ID PROCESS   STATUS
---------- --------- ------------
	 1 ARCH      CLOSING
	 1 ARCH      CONNECTED
	 1 ARCH      CONNECTED
	 1 ARCH      CLOSING
	 1 RFS	     IDLE
	 1 RFS	     IDLE
	 1 RFS	     IDLE
	 1 MRP0      APPLYING_LOG