ORACLE单实例ADG搭建
宫子晋
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.
SYS@PROD5 >alter database open;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING
这里要根据实际的情况添加一般比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.修改主库参数
SYS@PROD5 >create pfile='/home/oracle/pfile.ora' from spfile
[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
SYS@PROD5 >create spfile from pfile='/home/oracle/pfile.ora';
SYS@PROD5 >startup
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与监听文件
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)
)
)
主：
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)
)
)
[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参数
[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
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
类似资料：