一、环境准备
二、配置步骤
2.1 主库配置
2.1.1 开启监听
[oracle@ORCL ~]$ lsnrctl start
2.1.2 确保主库开启归档模式
如图可知，未开启归档模式。
开启归档：
将数据库启动到mount状态：
创建存放归档日志的文件：
[oracle@ORCL ORCL]$ pwd
/u01/app/oracle/oradata/ORCL
[oracle@ORCL ORCL]$ mkdir -p archivelog
设置归档文件存放路径并开启归档：
归档开启成功。
2.1.3 确保主库 force logging mode
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
2.1.4 拷贝主库密码文件到备库
[oracle@ORCL ~]$ scp $ORACLE_HOME/dbs/orapworcl oracle@192.168.198.143:$ORACLE_HOME/dbs/orapworcldg
oracle@192.168.198.143's password:
orapworcl 100% 2048 2.1MB/s 00:00
到备库检查一下：
2.1.5 配置tnsnames.ora并拷贝到备库
[oracle@ORCL ~]$ cd $ORACLE_HOME/network/admin/
[oracle@ORCL admin]$ vi tnsnames.ora
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORCL_DG)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
//将tnsname.ora文件传输到备库：
[oracle@ORCL admin]$ scp tnsnames.ora oracle@192.168.198.143:$ORACLE_HOME/network/admin/
oracle@192.168.198.143's password:
tnsnames.ora 100% 567 450.8KB/s 00:00
到备库对tnsname.ora文件稍作修改：
2.1.6 主库创建pfile文件，并传到备库中
SQL> create pfile='/home/oracle/mespfile.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ORCL ~]$ cd /home/oracle
[oracle@ORCL ~]$ ls
Desktop Downloads Music Public Videos
Documents mespfile.ora Pictures Templates
[oracle@ORCL ~]$ scp mespfile.ora oracle@192.168.198.143:/home/oracle
oracle@192.168.198.143's password:
mespfile.ora 100% 1056 847.4KB/s 00:00
2.1.7 在主库中增加参数
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=both;
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=orcldg async lgwr valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=both ;
System altered.
SQL> alter system set fal_server=orcldg scope=both;
System altered.
SQL> alter system set fal_client=orcl scope=both;
System altered.
SQL> alter system set standby_file_management=auto scope=both ;
System altered.
2.1.8 主库做全备,并拷贝至备库
创存放备份文件的文件夹：
[oracle@ORCL ~]$ mkdir -p backup
rman全备份：
[oracle@ORCL ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 25 14:03:13 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1646698371)
RMAN> run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup database format '/home/oracle/backup/backdata_%d_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/backup/archlog_%d_%T_%U.bak';
backup current controlfile format '/home/oracle/backup/cntrl_%s_%p_%s.bak';
release channel c1;
release channel c2;
}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=400 device type=DISK
allocated channel: c2
channel c2: SID=25 device type=DISK
Starting backup at 2022-10-25 14:04:09
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel c1: starting piece 1 at 2022-10-25 14:04:10
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel c2: starting piece 1 at 2022-10-25 14:04:10
channel c1: finished piece 1 at 2022-10-25 14:05:21
piece handle=/home/oracle/backup/backdata_ORCL_20221025_011b5m2q_1_1.bak tag=TAG20221025T140410 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:11
channel c2: finished piece 1 at 2022-10-25 14:05:21
piece handle=/home/oracle/backup/backdata_ORCL_20221025_021b5m2q_1_1.bak tag=TAG20221025T140410 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:11
Finished backup at 2022-10-25 14:05:21
Starting Control File and SPFILE Autobackup at 2022-10-25 14:05:21
piece handle=/u01/app/oracle/product/19c/db_1/dbs/c-1646698371-20221025-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2022-10-25 14:05:28
sql statement: alter system archive log current
Starting backup at 2022-10-25 14:05:33
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=1119016419
channel c1: starting piece 1 at 2022-10-25 14:05:34
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=2 STAMP=1119017133
input archived log thread=1 sequence=8 RECID=3 STAMP=1119017133
channel c2: starting piece 1 at 2022-10-25 14:05:34
channel c1: finished piece 1 at 2022-10-25 14:05:35
piece handle=/home/oracle/backup/archlog_ORCL_20221025_041b5m5e_1_1.bak tag=TAG20221025T140534 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 2022-10-25 14:05:35
piece handle=/home/oracle/backup/archlog_ORCL_20221025_051b5m5e_1_1.bak tag=TAG20221025T140534 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-10-25 14:05:35
Starting backup at 2022-10-25 14:05:35
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 2022-10-25 14:05:36
channel c1: finished piece 1 at 2022-10-25 14:05:37
piece handle=/home/oracle/backup/cntrl_6_1_6.bak tag=TAG20221025T140535 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-10-25 14:05:37
Starting Control File and SPFILE Autobackup at 2022-10-25 14:05:37
piece handle=/u01/app/oracle/product/19c/db_1/dbs/c-1646698371-20221025-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2022-10-25 14:05:38
released channel: c1
released channel: c2
将备份文件传到备库：
[oracle@ORCL ~]$ scp -r backup oracle@192.168.198.143:/home/oracle/
oracle@192.168.198.143's password:
backdata_ORCL_20221025_011b5m2q_1_1.bak 100% 778MB 86.3MB/s 00:09
backdata_ORCL_20221025_021b5m2q_1_1.bak 100% 395MB 14.1MB/s 00:28
archlog_ORCL_20221025_041b5m5e_1_1.bak 100% 12MB 4.0MB/s 00:03
archlog_ORCL_20221025_051b5m5e_1_1.bak 100% 385KB 384.8KB/s 00:01
cntrl_6_1_6.bak 100% 10MB 1.5MB/s 00:06
2.2 备库配置
2.2.1 修改mespfile.ora参数文件
修改前：
修改后：
//添加了如下参数
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=orcldg
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.fal_server='orcl'
*.fal_client='orcldg'
*.standby_file_management=auto
创建文件中对应的路径：
[oracle@orcldg ~]$ mkdir -p /u01/app/oracle/admin/orcldg/adump
[oracle@orcldg ~]$ mkdir -p /u01/app/oracle/oradata/ORCLDG/
[oracle@orcldg ~]$ mkdir -p /u01/app/oracle/oradata/ORCLDG/archivelog
2.2.2 备库启动监听
[oracle@orcldg backup]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-OCT-2022 14:20:23
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19c/db_1//bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/orcldg/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcldg)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 25-OCT-2022 14:21:11
Uptime 0 days 0 hr. 1 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/orcldg/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcldg)(PORT=1521)))
The listener supports no services
The command completed successfully
将环境变量中的ORACLE_SID改为orcldg:
环境变量重新生效一下：
2.2.3 启动到nomount状态
备库通过mespfile.ora启动到nomount状态，创建spfile文件，然后通过spfile启动到nomount：
[oracle:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 25 14:29:25 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/mespfile.ora';
ORACLE instance started.
Total System Global Area 1191181696 bytes
Fixed Size 8895872 bytes
Variable Size 738197504 bytes
Database Buffers 436207616 bytes
Redo Buffers 7880704 bytes
SQL> create spfile from pfile='/home/oracle/mespfile.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1191181696 bytes
Fixed Size 8895872 bytes
Variable Size 738197504 bytes
Database Buffers 436207616 bytes
Redo Buffers 7880704 bytes
2.2.4 备库恢复控制文件,数据库启动到mount状态
之前主机名为orcldg，忘记改了，在这里需要修改一下。
[oracle@ORCL_DG ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 25 15:35:22 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore standby controlfile from '/home/oracle/backup/cntrl_6_1_6.bak';
Starting restore at 2022-10-25 15:35:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=390 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCLDG/control01.ctl
output file name=/u01/app/oracle/oradata/ORCLDG/control02.ctl
Finished restore at 2022-10-25 15:35:58
数据库启动到mount状态:
SQL> alter database mount;
2.2.5 测试主库和备库的连通性
主库连备库：
[oracle@orcldg ~]$ sqlplus sys/123456@orcldg as sysdba
备库连主库：
[oracle@orcldg ~]$ sqlplus sys/123456@orcl as sysdba
2.2.6 备库恢复数据库
Catalog注册备份：
RMAN> catalog start with '/home/oracle/backup';
crosscheck核对数据文件备份集：
RMAN> crosscheck backup;
备库恢复数据库:
RMAN> run{
set newname for database to '/u01/app/oracle/oradata/ORCLDG/%b';
restore database;
switch datafile all;
}
2.2.7 备库创建standby redo logs
alter database add standby logfile thread 1 group 11('/u01/app/oracle/oradata/ORCLDG/standby_redo_logs/redo11.log') size 200M;
alter database add standby logfile thread 1 group 12('/u01/app/oracle/oradata/ORCLDG/standby_redo_logs/redo12.log') size 200M;
alter database add standby logfile thread 1 group 13('/u01/app/oracle/oradata/ORCLDG/standby_redo_logs/redo13.log') size 200M;
备库开启应用：
alter database recover managed standby database using current logfile disconnect;
查看一下相关进程：
MRP的状态是WAIT_FOR_LOG。
重启一下数据库：
查看一下OPEN_MODE发现此时是READ ONLY状态：
再看一下进程，有RFS进程：
再开启一下应用：
再看一下相关进程：
发现有MRP进程，且状态为APPLYING_LOG，则搭建成功。
2.2.8 测试
在主库创建一张空表：
create table person
(
pid varchar(18),
name varchar(20) not null,
age number(3) not null check(age between 0 and 150),
birthday date,
address varchar2(200),
constraint person_pid_pk primary key(pid),
constraint person_name_uk unique(name)
);
切换到备库，查看一下这张表的结构：
发现可以查到。
至此，单实例的ADG搭建成功，如果有什么问题可以留言或私信讨论，希望可以跟大家一起进步。
2441
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
