部署拓扑
使用 RMAN "from service" 子句,可以在整个网络中将主数据库文件还原和恢复到备用数据库。您可以使用此功能来实例化备用数据库,而不是 RMAN DUPLICATE DATABASE
命令,并且更加直观且容易出错,从而节省时间。
注意:
单击复制可将命令示例保存到剪贴板,以便粘贴到命令行中。请务必将任何variables
替换为特定于实施的值。
使用 RMAN 创建备用数据库
使用 Oracle Recovery Manager (RMAN) 从活动主数据库创建备用数据库。或者,您可以从主数据库的备份还原。
- 连接到数据库并在
NOMOUNT
模式下启动数据库。$ rman target /
startup nomount;
- 从主服务恢复备用控制文件。在此示例中,DBUKS_898_LHR 是主数据库:
输出应如下所示:restore standby controlfile from service 'DBUKS_898_LHR';
Starting restore at 04-JUL-23 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=28 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output file name=+RECO/DBUKS_R2J_AMS/CONTROLFILE/current.256.1139953721 Finished restore at 04-JUL-23
- Mount the database:
输出应如下所示:alter database mount;
released channel: ORA_DISK_1 Statement processed
- 以 OCI 实例的 root 用户身份,将 Oracle 目录的权限更改为打开 (
chmod 777
)。cd /opt/
ls -ltra
请参见:drwxr-xr-x 10 root root 4096 Jun 20 03:52 oracle
输入:[root@ldbuksdr]# chmod 777 oracle/
ls -ltra
请参见:drwxrwxrwx 10 root root 4096 Jun 20 03:52 oracle
- 从主数据库还原数据库 (DBUKS_898_LHR):
输出应如下所示:restore database from service ' DBUKS_898_LHR ';
Starting restore at 04-JUL-23 using target database control file instead of recovery catalog allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=162 device type=SBT_TAPE channel ORA_SBT_TAPE_1: Oracle Database Backup Service Library VER=19.0.0.1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=28 device type=DISK channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00001 to +DATA/DBUKS_898_LHR/DATAFILE/system.261.1139943103 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to +DATA/DBUKS_898_LHR/DATAFILE/sysaux.268.1139943085 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:38 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00004 to +DATA/DBUKS_898_LHR/DATAFILE/undotbs1.260.1139943133 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:04 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00005 to +DATA/DBUKS_898_LHR/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/system.264.1139942759 channel ORA_DISK_1: restore complete, elapsed time: 00:00:49 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00006 to +DATA/DBUKS_898_LHR/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/sysaux.265.1139942759 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00007 to +DATA/DBUKS_898_LHR/F9D6EA8CCAA09630E0530905F40A5107/DATAFILE/undotbs1.266.1139942759 channel ORA_DISK_1: restore complete, elapsed time: 00:00:18 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/system.273.1139943583 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:04 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00009 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/sysaux.271.1139943599 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:16 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00010 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/undotbs1.270.1139943611 channel ORA_DISK_1: restore complete, elapsed time: 00:00:20 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: using network backup set from service DBUKS_898_LHR channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00011 to +DATA/DBUKS_898_LHR/DATAFILE/users.269.1139943873 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: using network backup set from service DBUKS_898_LHR channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00012 to +DATA/DBUKS_898_LHR/FE812140C0716067E053F6005A0AEB32/DATAFILE/users.274.1139943875 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02 Finished restore at 04-JUL-23
exit ;
验证备用数据库
接下来,您需要验证备用数据库。
- 查看数据库名称和角色。在
SQL>
提示符下,输入:select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE from v$database ;
您应看到类似的响应:
FORCE_LOGGING FLASHBACK_ON OPEN_MODE DATABASE_ROLE DATAGUARD_BROKER PROTECTION_MODE -------------------- ------------------ --------------- ------------------- ------------------------ ---------------------------- YES NO MOUNTED PHYSICAL STANDBY DISABLED MAXIMUM PERFORMANCE
- 查看数据库的归档进程、状态线程编号和序列号:
select sysdate,process,status,thread#,sequence#,block# from v$managed_standby where status!='IDLE';
您应看到类似的响应:
04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0
输入:
select distinct process from gv$managed_standby;
您应看到类似的响应:
PROCESS --------- ARCH DGRD
将备用日志文件添加到主数据库和备用数据库
备用数据库上的备用日志文件用于接收来自主数据库的重做数据。写入主数据库的联机重做日志的记录将传输到备用数据库,并同时写入备用重做日志,从而最大程度地降低备用数据库上数据丢失的概率。备用重做日志是实时应用的必备项。
使用
SQL>
命令提示符执行以下步骤。
- 首先,将备用日志文件添加到主数据库。
- 输入:
select group#, type, member from v$logfile;
您将看到类似以下内容的响应:
GROUP# TYPE MEMBER ---------- ------- --------------------------------------------------------- 3 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_3.259.1139942665 2 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_2.258.1139942665 1 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_1.257.1139942665
- 输入:
select bytes, group# from v$log;
您将看到类似以下内容的响应:1073741824 1 1073741824 3 1073741824 2
- 输入此系列命令:
SQL> alter database add standby logfile thread 1 group 5 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 6 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 7 ('+RECO') size 1073741824;
每种方法的响应应是:Database altered
- 输入:
select group#, type, member from v$logfile;
您将看到类似以下内容的响应:GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------- 3 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_3.259.1139942665 2 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_2.258.1139942665 1 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_1.257.1139942665 4 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_4.432.1141789993 5 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_5.431.1141790001 6 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_6.430.1141790011 7 STANDBY +RECO/DBUKS_898_LHR/ONLINELOG/group_7.430.1141790017
- 归档日志列表:
archive log list
- 输入:
- 现在,将备用日志文件添加到备用数据库。
- 输入:
select group#, type, member from v$logfile;
您将看到类似以下内容的响应:GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------ 3 ONLINE +RECO/DBUKS_R2J_AMS/ONLINELOG/group_3.489.1141280529 2 ONLINE +RECO/DBUKS_R2J_AMS/ONLINELOG/group_2.488.1141280519 1 ONLINE +RECO/DBUKS_R2J_AMS/ONLINELOG/group_1.487.1141280511
- 输入:
select bytes, group# from v$log;
您将看到类似以下内容的响应:1073741824 1 1073741824 3 1073741824 2
- 输入此系列命令:
alter database add standby logfile thread 1 group 4 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 5 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 6 ('+RECO') size 1073741824;
alter database add standby logfile thread 1 group 7 ('+RECO') size 1073741824;
每个命令的响应应为:Database altered
- 输入:
select group#, type, member from v$logfile;
您将看到类似以下内容的响应:GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------ 3 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_3.489.1141280529 2 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_2.488.1141280519 1 ONLINE +RECO/DBUKS_898_LHR/ONLINELOG/group_1.487.1141280511 4 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_4.294.1141283919 5 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_5.295.1141283929 6 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_6.296.1141283937 7 STANDBY +DATA/DBUKS_R2J_AMS/ONLINELOG/group_7.296.1141283938
- 输入:
- 删除具有主数据库唯一名称的日志文件。在三个日志文件中,其中一个文件将是当前日志,因此此时将无法删除它;此文件将在以后删除并重新创建。
- 输入:
alter database drop logfile group 1;
您将看到类似以下内容的响应:alter database drop logfile group 1 * ERROR at line 1: ORA-01623: log 1 is current log for instance dbuks (thread 1) - cannot drop ORA-00312: online log 1 thread 1: '+RECO/DBUKS_898_LHR/ONLINELOG/group_1.257.1139942665'
- 输入:
alter database drop logfile group 2;
alter database drop logfile group 3;
每种方法的响应应是:Database altered
- 输入:
- 通过输入以下命令重新创建日志文件:
alter database add logfile thread 1 group 2 ('+RECO') size 1073741824;
alter database add logfile thread 1 group 3 ('+RECO') size 1073741824;
每种方法的响应应是:Database altered.
配置 Oracle Data Guard 中介
现在,配置 Oracle Data Guard 中介,注册备用数据库,并同步主数据库和备用数据库。通过在主数据库和备用数据库上启用 dg_broker_config_file 参数来配置 Oracle Data Guard 中介。对于 Oracle Automatic Storage Management (Oracle ASM),将中介配置文件放置在单独的磁盘组上。对于 Oracle Real Application Clusters (Oracle RAC),中介配置文件必须位于共享存储上。
- 检查主数据库和备用数据库的 dg_broker_start 值。确保将其设置为 True;在 SQL 命令行中,输入:
show parameter dg_broker_start;
输出应如下所示:NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUE
如果 dg_broker_start 设置为FALSE
,则在继续执行后续步骤之前,将该参数设置为TRUE
;输入:alter system set dg_broker_start=true;
输出应如下所示:select pname from v$process where pname like 'DMON%';
PNAME ----- DMON
- 检查主数据库的 Oracle Data Guard 文件:
show parameter dg_broker_config_file1;
如果具有 Oracle RAC 或 Oracle ASM,则可以更改配置文件位置;例如:show parameter dg_broker_config_file2;
alter system set dg_broker_config_file1=broker_config_file_location;
alter system set dg_broker_config_file2=broker_config_file_location;
- 注册主数据库和备用数据库:使用 Oracle Data Guard 命令行界面 (command-line interface,DGMGRL) 注册主数据库并将备用数据库配置文件添加到中介配置:
- 以 sys 身份登录主主机。
dgmgrl sys/password@net_service_name_for_primary
- 创建使用主数据库名称的配置。
CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS primary_database_name CONNECT IDENTIFIER IS primary_database_name;
输出应如下所示:CREATE CONFIGURATION dbuks_898_lhr_dbuks_r2j_ams AS PRIMARY DATABASE IS dbuks_898_lhr CONNECT IDENTIFIER IS dbuks_898_lhr;
- 添加备用数据库。
例如:ADD DATABASE standby unique database name AS CONNECT IDENTIFIER IS standby unique database name MAINTAINED AS PHYSICAL;
ADD DATABASE dbuks_r2j_ams AS CONNECT IDENTIFIER IS dbuks_r2j_ams MAINTAINED AS PHYSICAL;
输出应如下所示:Database "dbuks_r2j_ams" added
- 以 sys 身份登录主主机。
- 启用配置:
enable configuration;
- 显示配置:
- 输入:
show configuration;
注意:
如果收到无法确定应用滞后的警告,请通过 sqlplus 登录到主数据库并执行一些日志切换。Configuration - dbuks_898_lhr_dbuks_r2j_ams Protection Mode: MaxPerformance Members: dbuks_898_lhr - Primary database dbuks_r2j_ams - Physical standby database Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 36 seconds ago)
- 显示主数据库的配置(在本例中为 dbuks_898_lhr):
您将看到类似的响应:show database dbuks_898_lhr
Database - dbuks_898_lhr Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): dbuks Database Status: SUCCESS
- 显示备用数据库的配置(在本例中为 dbuks_r2j_ams):
您将看到类似的响应:show database dbuks_r2j_ams
Database - dbuks_r2j_ams Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 0 seconds ago) Average Apply Rate: 59.00 KByte/s Real Time Query: ON Instance(s): dbuks Database Status: SUCCESS
- 输入:
确认复制
备用数据库现在应开始应用从主数据库接收的重做。
使用
SQL>
命令提示符执行以下步骤。
- 检查 MRP 进程:
- 输入:
select sysdate, process, status, thread#, sequence#, block# from v$managed_standby where status!='IDLE';
输出应如下所示:SYSDATE PROCESS STATUS THREAD# SEQUENCE# BLOCK# --------- --------- ------------ ---------- ---------- ---------- 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 RFS RECEIVING 1 417 2413 10-JUL-23 MRP0 APPLYING_LOG 1 417 2412
- 查看 Data Guard 进程。
输出应如下所示:select distinct process from gv$managed_standby;
PROCESS --------- DGRD RFS MRP0 ARCH
- 备用数据库现在已开始应用重做日志。在主数据库上执行一些日志切换并在备用数据库上再次确认:
输出应如下所示:select sysdate, process, status, thread#, sequence#, block#from v$managed_standbywhere status!='IDLE';
SYSDATE PROCESS STATUS THREAD# SEQUENCE# BLOCK# --------- --------- ------------ ---------- ---------- ---------- 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 DGRD ALLOCATED 0 0 0 04-JUL-23 ARCH CLOSING 1 253 1 04-JUL-23 ARCH CONNECTED 0 0 0 04-JUL-23 ARCH CLOSING 1 252 1 04-JUL-23 MRP0 APPLYING_LOG 1 254 17 04-JUL-23 RFS RECEIVING 1 254 18
- 归档主数据库上的日志列表:
输出应如下所示:archive log list
输入:Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 415 Next log sequence to archive 417 Current log sequence 417
alter system switch logfile;
您将收到以下响应:System altered.
输入:/
请参见:System altered.
现在,归档日志列表:
您将收到以下响应:archive log list
Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 417 Next log sequence to archive 419 Current log sequence 419
- 归档备用数据库上的日志列表;输入:
select sysdate, process, status, thread#, sequence#, block# from v$managed_standby where status!='IDLE';
您将收到以下响应:
SYSDATE PROCESS STATUS THREAD# SEQUENCE# BLOCK# --------- --------- ------------ ---------- ---------- ---------- 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 DGRD ALLOCATED 0 0 0 10-JUL-23 ARCH CLOSING 1 418 1 10-JUL-23 ARCH CONNECTED 0 0 0 10-JUL-23 ARCH CLOSING 1 417 2048 10-JUL-23 RFS RECEIVING 1 419 59 10-JUL-23 MRP0 APPLYING_LOG 1 419 58
- 输入:
- 在备用数据库上启用闪回:
- 输入:
每个命令后,您应看到:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel; SQL> alter database flashback on;
Database altered
- 确认已启用(开启)数据库闪回。
输出应如下所示:select flashback_on from v$database;
FLASHBACK_ON ------------------ YES
- 输入:
请参见:recover managed standby database using current logfile disconnect from session;
Media recovery complete.
- 输入:
- 查看数据库详细信息:
输出应如下所示:select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE from v$database ;
FORCE_LOGGING FLASHBACK_ON OPEN_MODE DATABASE_ROLE DATAGUARD_BROKER PROTECTION_MODE ---------------------------------------------------------- --------------- ----- ----------------------- ----------------------------- YES YES MOUNTED PHYSICAL STANDBY ENABLED MAXIMUM PERFORMANCE
- 使用 Server Control 实用程序 (srvctl) 停止备用数据库:
输出应如下所示:srvctl stop database -d db_unique_name
srvctl stop database -d dbuks_r2j_ams
- 使用服务器控制实用程序启动备用数据库:
输出应如下所示:srvctl start database -d db_unique_name
srvctl start database -d dbuks_r2j_ams
- 查看数据库详细信息:
输出应如下所示:select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE from v$database ;
FORCE_LOGGING FLASHBACK_ON OPEN_MODE DATABASE_ROLE DATAGUARD_BROKER PROTECTION_MODE ----------------------------------------------------------------------- -------------------- ----------------------- YES YES READ ONLY WITH APPLY PHYSICAL STANDBY ENABLED MAXIMUM PERFORMANCE
- 现在,您可以从主数据库提供更多日志文件切换,并确保将重做应用到备用数据库。
- 重新创建具有主要唯一名称的暂挂日志文件。输入以下命令:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
alter database drop logfile group 1;
每个命令后,您应看到:alter database add standby logfile thread 1 group 1 ('+RECO') size 1073741824;
最后,输入:Database altered.
请参见:recover managed standby database using current logfile disconnect from session;
Media recovery complete.