oracle-data-guard
1. Dataguard 介绍
1.1 概念
Data Guard是保证企业数据的高可用性(high availability)、数据保护(data protection)以及灾难恢复(disaster recovery)的集成化灾难恢复解决方案。该技术可以维护生产数据库一个或多个同步备份，由一个生产数据库和若干个备用数据库组成，并形成一个独立的、易于管理的数据保护方案。Data Guard备用数据库可以与生产系统位于相同的数据中心，也可以是在地理位置上分布较远的的远程灾难备份中心。
1.2 原理
Data Guard的基本原理是：当某次事务处理对生产数据库中的数据作出更改时，Oracle 将在联机重做日志文件中记录此次更改。在 Data Guard中，除了把日志记录到本地的联机日志文件和归档日志文件中外，还通过网络，把日志信息发送到远程的备用数据库服务器上。这个备用日志文件写入过程可以是实时同步的，以实现零数据丢失(最大保护模式)；也可以是异步的，以减少对网络带宽的压力(最大可用性模式)；或者是通过归档日志文件的批量传输模式，以减少对生产系统的性能影响(最大性能模式)。当备份数据库接收到日志信息时，Data Guard 可以自动利用日志信息实现数据的同步。当主数据库打开并处于活动状态时，备份数据库可以执行恢复操作；如果主数据出现了故障，备用数据库即可以被激活并接管生产数据库的工作。
1.3 特点
-
需要冗余的服务器设备，该模式需要有冗余的服务器硬件，硬件成本较高。 -
需要冗余的存储设备，主机和备机都需要同样的存储空间，成本较高。 -
安装配置比较复杂，该模式比单节点、单实例的模式配置复杂一些，需要更多的配置步骤。 -
管理维护成本高，该模式对维护人员的要求较高，维护成本高。 -
具备一定的容灾特性，当主机数据库不可用，可以把数据库系统切换到备机上，具备容灾的功能。 -
备机可以用作只读查询，备机可以切换到只读状态供报表之类的查询操作，减轻主机的压力。
1.4 搭建 DG 方式
Data Guard备库按照从生产数据库传输过来的 日志处理方法的不同分为 物理备库、逻辑备库和快照备库。
1.4.1 物理备库
(三种方法：1、RMAN 的 Duplicate; 2、RMAN 的备份恢复; 3、DBCA )。
备份数据库处于 mount 状态下，直接利用数据恢复技术，把日志文件中记录的数据变更应用在备份数据库中，从而实现与生产数据库的数据同步，在进行数据同步的时候，物理备份数据库是不能打开的、也无法提供数据查询等服务；物理备用数据库也可以通过只读的方式打开，此时就只能接收日志文件，而无法进行数据反向同步。
1.4.2 逻辑备库
数据库是处于正常打开状态，当它接收到新的日志信息后，利用日志挖掘器的功能，把日志中记录的变更信息转换成具体的 SQL 语句，并在逻辑备用数据库上执行这些 SQL 语句，从而实现与生产数据库的数据同步。逻辑备份数据支持在数据同步时，进行数据的查询、报表等操作。Oracle 从 9i R2 开始支持逻辑备份数据库。
1.4.3 快照备库
可以暂时将物理备份数据库转换为可更新的数据库，快照备份数据不会立马应用接收到的日志文件，设一个时间点，一次性转换。
1.5 备库数据保护级别
Oracle Data Guard 支持多种级别的数据保护模式：最高性能模式(默认)，最大可用性模式，最大保护模式。分别对应于“重要信息系统灾难恢复指南”中的 5 级，5 级 6 级自适应，6 级的数据保护级别。其中对应 6 级的最大保护模式可以实现实时数据实时同步和 0 数据丢失。另外，Oracle Data Guard 可以设置延时应用时间窗口，从而防范错误操作、黑客攻击等人为错误导致的数据损坏。
1.5.1 保护模式和重做传输
要确定适当的保护模式，企业需要根据用户对系统响应时间的要求来估量它们对数据保护的业务要求。下表从数据丢失风险的角度概述了各种模式的适用性。
| 保护模式 | 在灾难出现时数据丢失的风险 | 重做传输机制 |
|---|---|---|
| 最大保护 | 零数据丢失 | LGWR SYNC |
| 最大可用 | 零数据丢失 | LGWR SYNC |
| 最高性能 | 最小数据丢失 | LGWR SYNC 或 ARCH |
1.5.2 最大保护模式
最大保护模式为主数据库提供了最高水平的数据保护，从而确保一个全面的零数据丢失的灾难恢复解决方案。当在最大保护模式下运行时，重做记录由日志写入器 (LGWR) 进程从主数据库同步地传输到备用数据库，并且直到确认事务数据在至少一个备用服务器上的磁盘上可用时，才在主数据库上提交事务。建议这种模式应至少配置两个备用数据库。当最后参与的备用数据库不可用时，主数据库上的处理将停止。这就确保了当主数据库与其所有备用数据库失去联系时，不会丢失事务。由于重做传输的同步特性，这种最大保护模式可能潜在地影响主数据库响应时间。可以通过配置一个低延迟网络，并为它分配足够应付高峰事务负载的带宽来将这种影响减到最小。需要这种最大保护模式的企业有股票交易所、货币交易所、金融机构等。
1.5.3 最大可用性模式
最高可用性模式拥有仅次于最高水平的主数据库数据可用性。如同最大保护模式一样，重做数据由 LGWR 从主数据库同步地传输到备用数据库，直到确认事务数据在备用服务器的磁盘上可用时，事务才在主数据库上完成。不过，在这种模式下（与最大保护模式不同），如果最后参与的备用数据库变为不可用，例如由于网络连接问题，处理将在主数据库上继续进行。备用数据库与主数据库相比，可能暂时落在后面，但当它再次变为可用时，备用数据库将使用主数据库上累积的归档日志自动同步，而不会丢失数据。由于同步重做传输，这种保护模式可潜在地影响响应时间和吞吐量。可以通过配置一个低延迟网络，并为它分配足够应付高峰事务负载的带宽来将这种影响减到最小。最高可用性模式适用于想要确保获得零数据丢失保护，但不想让生产数据库受网络/备用服务器故障影响的企业。如果又一个故障随后影响了生产数据库，然后最初的网络/备用服务器故障得到解决，那么这些企业将接受数据丢失的可能性。
1.5.4 最高性能模式
最高性能模式是默认的保护模式。它与最高可用性模式相比，提供了稍微少一些的主数据库数据保护，但提供了更高的性能。在这种模式下，当主数据库处理事务时，重做数据由 LGWR 进程异步传输到备用数据库上。另外，也可以将主数据库上的归档器进程 (ARCH) 配置为在这种模式下传输重做数据。在任何情况下，均先完成主数据库上的写操作，主数据库的提交操作不等待备用数据库确认接收。如果任意备用目标数据库变为不可用，则处理将在主数据库上继续进行，这对性能只有很小的影响或没有影响。在主数据库出现故障的情况下，尚未被发送到备用数据库的重做数据会丢失。但是，如果网络有足够的吞吐量来跟上重做流量高峰，并且使用了 LGWR 进程来将重做流量传输到备用服务器，则丢失的事务将非常少或者为零。当主数据库上的可用性和性能比丢失少量数据的风险更重要时，应该使用最高性能模式。这种模式还适合于 WAN 上的 Data Guard 部署，网络的内在延迟可能限制同步重做传输的适用性。
2. 配置结构图
2.1 Redo Transport Services
通过 redo 日志传输服务，将 redo 日志从主库传到备库相应位置。
2.2 Apply Services
备库应用主库传过来的 redo 日志。物理备库和逻辑备库的区别也是在这里区分的。
物理备库使用 Redo Apply技术，使用标准的 oracle 数据恢复功能，应用备库的 redo 日志
逻辑备库使用 SQL Apply技术，先将收到的 redo 日志解析为 sql 语句，然后在备库上执行。
2.3 Role Transitions
切换主备库的角色，主库变为备库或备库变为主库。
分为 switchover 和 failover ，区别是switchover 人为主动切换角色，failover 异常时自动切换角色。
3. 数据库实例
3.1 实例构成
The basic memory structures associated with Oracle Database include:
-
System global area (SGA)
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. All server and background processes share the SGA. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
-
Program global area (PGA)
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. Oracle Database creates the PGA when an Oracle process starts.
One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
-
User global area (UGA)
The UGA is memory associated with a user session.
-
Software code areas
Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.
The following figure illustrates the relationships among these memory structures.
3.2 实例文件
An Oracle database is a set of files that store Oracle data in persistent disk storage. This section discusses the database files generated when you issue a CREATE DATABASE statement:
-
Data files and temp files
A data file is a physical file on disk that was created by Oracle Database and contains data structures such as tables and indexes. A temp file is a data file that belongs to a temporary tablespace. The database writes data to these files in an Oracle proprietary format that cannot be read by other programs.
-
Control files
A control file is a root file that tracks the physical components of the database.
-
Online redo log files
The online redo log is a set of files containing records of changes made to data.
3.3 实例配置文件
启动实例必须读取 spfile(server parameter file,二进制文件)或 pfile(文本文件)。默认读取 spfile 启动。
spfile 通常位于 $ORACLE_HOME/dbs 目录下，采用 spfile<ORACLE_SID>.ora 格式.
pfile 通常位于 $ORACLE_HOME/dbs 目录下，采用 init <ORACLE_SID>.ora 格式.
spfile 文件是在安装数据库时创建的，pfile 文件不是自动创建的。pfile 文件是从 spfile 文件创建的，pfile 文件是文本文件，改起来更容易。
[oracle@dev2oracle dbs]$ realpath spfileorcl.ora
/home/oracle/app/product/12.2.0/dbhome_1/dbs/spfileorcl.ora
[oracle@dev2oracle dbs]$ strings spfileorcl.ora
## 配置文件中重要的参数，没有的话，启动报错
SQL> select name from v$parameter where isbasic='TRUE';
NAME
--------------------------------------------------------------------------------
processes
sessions
nls_language
nls_territory
sga_target
control_files
db_block_size
compatible
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1
NAME
--------------------------------------------------------------------------------
log_archive_dest_state_2
cluster_database
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest
db_recovery_file_dest_size
undo_tablespace
instance_number
ldap_directory_sysauth
remote_login_passwordfile
NAME
--------------------------------------------------------------------------------
db_domain
shared_servers
remote_listener
db_name
db_unique_name
open_cursors
star_transformation_enabled
pga_aggregate_target
数据实例运行过程中，对于初始化参数的配置和修改（带 scope=spfile），会保存到 spfile 中，但必须重启数据库才会生效。
通过 create pfile 才能将保存到 spfile 的配置保存过来。
startup 命令可以指定 pfile 或者 spfile，来使用特定的初始化配置文件来启动数据库实例。
The scope of a parameter change depends on when the change takes effect. When an instance has been started with a server parameter file, you can use the ALTER SYSTEM SET statement to change values for system-level parameters as follows:
-
SCOPE=MEMORYChanges apply to the database instance only. The change will not persist if the database is shut down and restarted.
-
SCOPE=SPFILEChanges apply to the server parameter file but do not affect the current instance. Thus, the changes do not take effect until the instance is restarted.
Note:
You must specify
SPFILEwhen changing the value of a parameter described as not modifiable in Oracle Database Reference. -
SCOPE=BOTHOracle Database writes changes both to memory and to the server parameter file. This is the default scope when the database is using a server parameter file.
The database prints the new value and the old value of an initialization parameter to the alert log. As a preventative measure, the database validates changes of basic parameter to prevent invalid values from being written to the server parameter file.
3.4 实例启动过程
| Phase | Mount State | Description | |
|---|---|---|---|
| 1 | Instance started without mounting database | The instance is started, but is not yet associated with a database. | |
| 2 | Database mounted | The instance is started and is associated with a database by reading itscontrol file. The database is closed to users. | |
| 3 | Database open | The instance is started and is associated with an open database. The data contained in the data files is accessible to authorized users. |
3.5 实例停止过程
| Phase | Mount State | Description |
|---|---|---|
| 1 | Database closed | The database is mounted, but online data files and redo log files are closed. |
| 2 | Database unmounted | The instance is started, but is no longer associated with the control file of the database. |
| 3 | Database instance shut down | The database instance is no longer started. |
shutdown命令参数效果
| Database Behavior | ABORT | IMMEDIATE(常用) | TRANSACTIONAL | NORMAL |
|---|---|---|---|---|
| Permits new user connections | No | No | No | No |
| Waits until current sessions end | No | No | No | Yes |
| Waits until current transactions end | No | No | Yes | Yes |
| Performs acheckpoint and closes open files | No | Yes | Yes | Yes |
3.6 实例常用命令
# 监听状态
lsnrctl status
# 监听启动
lsnrctl start
# 监听停止
lsnrctl stop
# 停止实例
SHUTDOWN IMMEDIATE;
# 启动实例
STARTUP;
STARTUP NOMOUNT;
# 备库
## 关闭备库实例
alter database recover managed standby database cancel; --停止同步
shutdown immediate;
## 启动备库实例
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
# 参数
DESC V$PARAMETER;
SELECT name, default_value FROM v$parameter;
show parameter XXX(可模糊匹配);
# spfile和pfile互转（默认不用指定名称，startup默认使用spfile）
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/app/product/12.2.
0/dbhome_1/dbs/spfileorcl.ora
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/app/product/12.2.
0/dbhome_1/dbs/spfileorcl.ora
CREATE PFILE = 'my_init.ora' FROM SPFILE = 's_params.ora';
CREATE SPFILE = 's_params.ora' FROM PFILE = '$ORACLE_HOME/work/t_init1.ora';
3.7 常用日志
Automatic Diagnostic Repository (ADR) is a file-based repository that stores database diagnostic data such as trace files, the alert log, DDL log, and Health Monitor reports.
自动诊断库是一个文件库，存储着各种诊断文件，包括跟踪文件、警告日志，数据定义日志和健康监控报告等。自动诊断库是在实例创建时就存在的，早于具体库的创建。
# 查看日志目录
SQL> SELECT NAME, VALUE FROM V$DIAG_INFO;
NAME VALUE
--------------------- --------------------------------------------------------
Diag Enabled TRUE
ADR Base /d1/3910926111/oracle/log
ADR Home /d1/3910926111/oracle/log/diag/rdbms/dbn/osi
Diag Trace /d1/3910926111/oracle/log/diag/rdbms/dbn/osi/trace
Diag Alert /d1/3910926111/oracle/log/diag/rdbms/dbn/osi/alert
Diag Incident /d1/3910926111/oracle/log/diag/rdbms/dbn/osi/incident
Diag Cdump /d1/3910926111/oracle/log/diag/rdbms/dbn/osi/cdump
Health Monitor /d1/3910926111/oracle/log/diag/rdbms/dbn/osi/hm
Default Trace File /d1/3910926111/oracle/log ... osi/trace/osi_ora_6825.trc
Active Problem Count 0
Active Incident Count 0
3.7.1 Alert Log
alert log 是 xml 文件，在alert目录下。记录启动实例后的内容。具体内容如下：
The alert log contents include the following:
-
All internal errors ( ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) -
Administrative operations such as the SQL*Plus commands STARTUP,SHUTDOWN,ARCHIVE LOG, andRECOVER -
Several messages and errors relating to the functions of shared server and dispatcher processes -
Errors during the automatic refresh of a materialized view
在trace目录下也有一个 alert 文件。对于排查问题也很重要。 ll | grep alert
3.7.2 Trace Files
A trace file is a file that contains diagnostic data used to investigate problems. Also, trace files can provide guidance for tuning applications or an instance.
ADR stores trace files in the trace subdirectory. Trace file names are platform-dependent and use the extension .trc.
Typically, database background process trace file names contain the Oracle SID, the background process name, and the operating system process number. An example of a trace file for the RECO process is mytest_reco_10355.trc.
Server process trace file names contain the Oracle SID, the string ora, and the operating system process number. An example of a server process trace file name is mytest_ora_10304.trc.
Sometimes trace files have corresponding trace metadata files, which end with the extension .trm. These files contain structural information called trace maps that the database uses for searching and navigation。
#To find the trace file for your current session:
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/home/oracle/app/diag/rdbms/primary/orcl/trace/orcl_ora_13826.trc
#To find all trace files for the current instance:
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
VALUE
--------------------------------------------------------------------------------
/home/oracle/app/diag/rdbms/primary/orcl/trace
4. 环境准备
4.1 环境规划
实验基于单机 DB+单实例 DG。是 NO-CDB 方式的。
注：备库中的实例名 SID 是参数设置的，SID 可以和主库相同。
| 主库(主机 1) | 备库(主机 2) | |
|---|---|---|
| 名称 | 主库 | 备库 |
| DB 类型 | 单机 | 单机 |
| OS | Centos 7.8 | Centos 7.8 |
| Hostname | testServer | dev2oracle |
| IP | 10.115.13.137 | 10.115.28.224 |
| DB_Version | 12.2.0.1.0 | 12.2.0.1.0 |
| ORACLE_BASE | /home/oracle/app/ | /home/oracle/app/ |
| ORACLE_HOME | /home/oracle/app/product/12.2.0/dbhome_1/ | /home/oracle/app/product/12.2.0/dbhome_1/ |
| DB_NAME | orcl | orcl |
| ORACLE_SID | orcl | orcl |
| DB_Unique_Name | orcl | orcl |
| Instance_Name | orcl | orcl |
| service_names | primary | standby |
| TNS_Name | PRIMARY | STANDBY |
| 闪回区 | 开启 | 开启 |
| 归档 | 开启 | 开启 |
4.2 数据库安装(略)
-
在主库上安装数据库软件，并建监听和实例。 -
在备库上安装数据库软件，并建监听，但不创建实例，安装时选择只安装软件即可。
4.3 补充
SQL> set linesize 200;
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
SQL>
5. 主库配置
5.1 主库启动 FORCE LOGGING
SQL> select open_mode,log_mode,database_role from v$database;
OPEN_MODE LOG_MODE DATABASE_ROLE
-------------------- ------------ ----------------
READ WRITE NOARCHIVELOG PRIMARY
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL>
5.2 主库启动归档模式
5.2.1 开启归档日志模式
1、登录服务端：sqlplus / as sysdba;
2、查询归档模是否开启：archive log list;
3、关闭数据库：shutdown immediate;
4、开启数据库至 mount 状态：startup mount;
5、修改数据库模式：alter database archivelog;
6、开启数据库：alter database open;
7、查看归档日志是否开启：archive log list;
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2322
Current log sequence 2324
SQL> shutdown immediate；
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8621184 bytes
Variable Size 1006633856 bytes
Database Buffers 570425344 bytes
Redo Buffers 8155136 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2322
Next log sequence to archive 2324
Current log sequence 2324
SQL>
5.2.2 关闭归档日志模式
1、登录服务端：sqlplus / as sysdba；
2、关闭数据库：shutdown immediate;
3、打开数据库：startup mount;
4、关闭归档日志：alter database noarchivelog;
5、开启数据库：alter database open;
6、查看归档日志是否关闭：archive log list ;
这里特别说明一下归档日志的存放位置。
Archive destination USE_DB_RECOVERY_FILE_DEST
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/app/fast_recovery
_area/orcl
db_recovery_file_dest_size big integer 100G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL> alter system set db_recovery_file_dest_size=100G;
System altered.
[oracle@testserver dbs]$ ll $ORACLE_BASE/fast_recovery_area
total 0
drwxr-x---. 4 oracle oinstall 33 Nov 17 17:25 orcl
[oracle@testserver dbs]$ ll $ORACLE_BASE/fast_recovery_area/orcl/
ORCL PRIMARY
[oracle@testserver dbs]$ ll $ORACLE_BASE/fast_recovery_area/orcl/ORCL/
archivelog autobackup controlfile onlinelog
[oracle@testserver dbs]$ ll $ORACLE_BASE/fast_recovery_area/orcl/ORCL/onlinelog
total 614412
-rw-r-----. 1 oracle oinstall 209715712 Nov 24 10:52 o1_mf_1_j4xokfy5_.log
-rw-r-----. 1 oracle oinstall 209715712 Nov 24 17:15 o1_mf_2_j4xokfyd_.log
-rw-r-----. 1 oracle oinstall 209715712 Nov 24 10:51 o1_mf_3_j4xokfyn_.log
[oracle@testserver dbs]$ ll $ORACLE_BASE/fast_recovery_area/orcl/ORCL/archivelog
total 4
drwxr-x---. 2 oracle oinstall 4096 Nov 16 22:26 2023_11_16
drwxr-x---. 2 oracle oinstall 108 Nov 17 16:13 2023_11_17
drwxr-x---. 2 oracle oinstall 40 Nov 21 14:04 2023_11_21
[oracle@testserver dbs]$ ll $ORACLE_BASE/fast_recovery_area/orcl/PRIMARY/
total 0
drwxr-x---. 10 oracle oinstall 150 Nov 24 09:30 archivelog
drwxr-x---. 3 oracle oinstall 24 Nov 24 10:35 autobackup
[oracle@testserver dbs]$ ll $ORACLE_BASE/fast_recovery_area/orcl/PRIMARY/archivelog
total 28
drwxr-x---. 2 oracle oinstall 210 Nov 17 23:16 2023_11_17
drwxr-x---. 2 oracle oinstall 176 Nov 18 22:38 2023_11_18
drwxr-x---. 2 oracle oinstall 210 Nov 19 23:10 2023_11_19
drwxr-x---. 2 oracle oinstall 142 Nov 20 23:47 2023_11_20
drwxr-x---. 2 oracle oinstall 4096 Nov 21 23:05 2023_11_21
drwxr-x---. 2 oracle oinstall 4096 Nov 22 22:02 2023_11_22
drwxr-x---. 2 oracle oinstall 8192 Nov 23 23:08 2023_11_23
drwxr-x---. 2 oracle oinstall 4096 Nov 24 10:52 2023_11_24
[oracle@testserver dbs]$
5.3 开启闪回
# 1、查看闪回未开启
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------
NO
# 2、查看 db_recovery_file_dest 为空
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
remote_recovery_file_dest string
# 3、指定闪回区大小，指定闪回目录路径(顺序必须先设置闪回区大小，才能指定闪回目录，否则报错)
SQL> alter system set db_recovery_file_dest_size=200g;
System altered.
#如果有就不指定，再继续修改目录位置，修改完目录需要重启实例
# 4、先创建目录，在指定该闪回区域目录
mkdir -p /home/oracle/app/fast_recovery_area/orcl
SQL> alter system set db_recovery_file_dest='/home/oracle/app/fast_recovery_area/orcl';
System altered.
SQL> shutdown immediate;
SQL> startup;
# 5 开启闪回
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
5.4 主库添加 standby redo logfile
为主库添加
standby redo log后，备库自动同步，所以备库不用再创建standby redo log。Data Guard 在最大保护和最高可用性模式下，Standby 数据库必须配置
standby redo log。
以下非常重要！！！ 否则可能会遇到不能实时同步的情况，必须使用 alter system switch logfile才能同步的情况！！！
确保Standby redo log的大小与主库online redo log（已存在的）的大小一致：
-
如果主库为单实例数据库： Standby redo log组数=主库日志总数+1。 -
如果主库是 RAC数据库：Standby redo log组数=(每线程的日志数+1)*最大线程数。 -
不建议复用 Standby redo log，避免增加额外的I/O以及延缓重做传输。
# 查看日志存放位置
SQL> select member from v$logfile;
SQL> select count(group#),thread# from v$log group by thread#;
COUNT(GROUP#) THREAD#
------------- ----------
3 1
# 查看日志块大小
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 200
2 200
3 200
# 添加 4(3+1)个standby logfile：需要注意块大小必须为200M
SQL> alter database add standby logfile '/home/oracle/app/oradata/ORCL/onlinelog/stdredo01.log' size 200M;
SQL> alter database add standby logfile '/home/oracle/app/oradata/ORCL/onlinelog/stdredo02.log' size 200M;
SQL> alter database add standby logfile '/home/oracle/app/oradata/ORCL/onlinelog/stdredo03.log' size 200M;
SQL> alter database add standby logfile '/home/oracle/app/oradata/ORCL/onlinelog/stdredo04.log' size 200M;
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
---------- ---------------
8 200
9 200
10 200
11 200
# 再查询会包括原来的6个online log 和新增的 4个redo log.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/home/oracle/app/oradata/ORCL/onlinelog/o1_mf_3_j4xokf04_.log
3 ONLINE
/home/oracle/app/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_3_j4xokfyn_.log
2 ONLINE
/home/oracle/app/oradata/ORCL/onlinelog/o1_mf_2_j4xokdy3_.log
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
2 ONLINE
/home/oracle/app/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_2_j4xokfyd_.log
1 ONLINE
/home/oracle/app/oradata/ORCL/onlinelog/o1_mf_1_j4xokdws_.log
1 ONLINE
/home/oracle/app/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_1_j4xokfy5_.log
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
8 STANDBY
/home/oracle/app/oradata/ORCL/onlinelog/stdredo11.log
9 STANDBY
/home/oracle/app/oradata/ORCL/onlinelog/stdredo12.log
10 STANDBY
/home/oracle/app/oradata/ORCL/onlinelog/stdredo13.log
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
11 STANDBY
/home/oracle/app/oradata/ORCL/onlinelog/stdredo14.log
10 rows selected.
SQL>
5.5 主库配置监听
[oracle@testserver ~]$ cd $ORACLE_HOME/network/admin
[oracle@testserver admin]$ ll
total 264
-rw-r--r--. 1 oracle oinstall 517 Nov 21 17:18 listener.ora
drwxr-xr-x. 2 oracle oinstall 64 Mar 15 2021 samples
-rw-r--r--. 1 oracle oinstall 1441 Aug 28 2015 shrept.lst
-rw-r--r--. 1 oracle oinstall 32874 Mar 15 2021 sqlnet.ora
-rw-r--r--. 1 oracle oinstall 371 Nov 21 17:30 tnsnames.ora
[oracle@testserver admin]$ vi listener.ora
# listener.ora Network Configuration File: /home/oracle/app/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testServer)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY)
(ORACLE_HOME = /home/oracle/app/product/12.2.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = STANDBY)
(ORACLE_HOME = /home/oracle/app/product/12.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
[oracle@testserver admin]$ vi tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.115.13.137)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.115.28.244)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
# 最终效果
[oracle@testserver admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 24-NOV-2023 14:13:33
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testServer)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 22-NOV-2023 17:42:02
Uptime 1 days 20 hr. 31 min. 31 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/app/diag/tnslsnr/testserver/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testServer)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "primary" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@testserver admin]$
# 使用tnsping测试主备连通性
## 如果备库不通，试试将备库host改为ip。
[oracle@dev2oracle admin]$ tnsping primary
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 24-NOV-2023 15:31:36
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/home/oracle/app/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = testServer)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@dev2oracle admin]$ tnsping standby
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 24-NOV-2023 15:31:43
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/home/oracle/app/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dev2oracle)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@dev2oracle admin]$
5.6 主库修改配置
SQL> create pfile from spfile;
File created.
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/app/product/12.2.
0/dbhome_1/dbs/spfileorcl.ora
SQL>
[oracle@testserver dbs]$ ll
total 11944
-rw-r--r--. 1 oracle oinstall 3079 May 15 2015 init.ora
-rw-r--r--. 1 oracle oinstall 1545 Nov 24 15:39 initorcl.ora
-rw-r-----. 1 oracle oinstall 24 Mar 15 2021 lkORCL
-rw-r-----. 1 oracle oinstall 3584 Mar 15 2021 orapworcl
-rw-r-----. 1 oracle oinstall 4608 Nov 24 14:12 spfileorcl.ora
# 主库查看
SQL> show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string primary
SQL>
# 备库查看
SQL> show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string standby
SQL>
[oracle@testserver dbs]$ vi initorcl.ora
# 具体内容（拷贝时记得去掉没用的注释）
#SYNC: 主库commit之前，等待备库接受完日志数据，至少一个备库
#ASYNC: 主库commit之前，不等待
#AFFIRM: 日志信息写到备库standby_log后，通知主库日志就收完毕
#NOAFFIRM: 不用等日志信息写到备库standby日志，就通知主库日志接收完毕
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=335544320
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=16777216
orcl.__large_pool_size=83886080
orcl.__oracle_base='/home/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=553648128
orcl.__sga_target=1040187392
orcl.__shared_io_pool_size=50331648
orcl.__shared_pool_size=503316480
orcl.__streams_pool_size=33554432
*.audit_file_dest='/home/oracle/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/home/oracle/app/oradata/ORCL/controlfile/o1_mf_j4xokbro_.ctl','/home/oracle/app/fast_recovery_area/orcl/ORCL/controlfile/o1_mf_j4xokbsb_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oradata'
*.db_name='orcl'
*.db_recovery_file_dest='/home/oracle/app/fast_recovery_area/orcl'
# 主备模式对归档日志空间要求很大，注意调整
*.db_recovery_file_dest_size=100g
*.diagnostic_dest='/home/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1515m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
# 追加内容
*.db_unique_name='primary'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=primary'
# 第一个standby是备库tnsname.ora的连接名(最开头名称)
# 第二个standby是DB_UNIQUE_NAME
*.log_archive_dest_3='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=standby'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_3=enable
*.standby_file_management='auto'
*.DB_FILE_NAME_CONVERT='/orcl/','/standby/'
*.LOG_FILE_NAME_CONVERT='/orcl/','/standby/'
# fal表示fetch archive log
# fal_client用于发送日志，fal_server用于接受日志。也即无论是主库或备库，fal_server=对方，fal_client=自己
*.fal_server='standby'
*.fal_client='primary'
# 停止实例
SQL> shutdown immediate;
# 应用配置
SQL> create spfile from pfile;
SQL> startup;
5.7 拷贝主库口令文件到备库
[oracle@testserver dbs]$ scp orapworcl oracle@dev2oracle:$ORACLE_HOME/dbs
6. 备库配置
6.1 备库创建目录
[oracle@dev2oracle admin]$ mkdir -p /home/oracle/app/admin/orcl/adump
[oracle@dev2oracle admin]$ mkdir -p /home/oracle/app/fast_recovery_area/orcl
[oracle@dev2oracle admin]$ mkdir -p /home/oracle/app/oradata
[oracle@dev2oracle admin]$
6.2 备库配置监听
[oracle@dev2oracle admin]$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev2oracle)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /home/oracle/app/product/12.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
[oracle@dev2oracle admin]$ vi tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.115.13.137)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.115.28.244)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
[oracle@dev2oracle admin]$ tnsping primary
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-NOV-2023 20:06:30
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.115.13.137)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@dev2oracle admin]$ tnsping standby
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 28-NOV-2023 20:06:35
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.115.28.244)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@dev2oracle admin]$
6.3 基于主库配置备库（修改备库）
# 先复制主库初始化配置文件和密码文件到备库相应目录下
[oracle@testserver dbs]$cd $ORACLE_HOME/dbs
[oracle@testserver dbs]$ scp initorcl.ora oracle@dev2oracle:$ORACLE_HOME/dbs
[oracle@dev2oracle dbs]$ vi initorcl.ora
# 配置内容
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=335544320
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=16777216
orcl.__large_pool_size=83886080
orcl.__oracle_base='/home/oracle/app'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=553648128
orcl.__sga_target=1040187392
orcl.__shared_io_pool_size=50331648
orcl.__shared_pool_size=503316480
orcl.__streams_pool_size=33554432
*.audit_file_dest='/home/oracle/app/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
# 注意修改为备库对应的控制文件
*.control_files='/home/oracle/app/oradata/orcl/control01.ctl','/home/oracle/app/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oradata'
*.db_name='orcl'
*.db_recovery_file_dest='/home/oracle/app/fast_recovery_area/orcl'
# 主备对归档空间要求很大，注意调整
*.db_recovery_file_dest_size=100g
*.diagnostic_dest='/home/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1515m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_name='orcl'
# 追加内容
*.db_unique_name='standby'
*.log_archive_config='dg_config=(primary,standby)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=standby'
*.log_archive_dest_3='service=primary LGWR ASYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=primary'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_3=enable
# 此项设置为自动，不然在主库创建数据文件后，备库不会自动创建
*.standby_file_management='auto'
*.fal_server='primary'
*.fal_client='standby'
# 应用配置
SQL> create spfile from pfile;
File created.
SQL>
# 启动为nomout模式
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8621184 bytes
Variable Size 1191183232 bytes
Database Buffers 385875968 bytes
Redo Buffers 8155136 bytes
SQL>
7. Rman 同步（主—>备）
7.1 从主库拷贝到备库
# 备库启动为nomout模式
SQL>startup nomount
# 主库操作
# 使用rman 连接主库和备库，并设置好主备关系（target-主：auxiliary-备）
[oracle@testserver admin]$ rman target sys/Sunway612@primary auxiliary sys/Sunway612@standby
Recovery Manager: Release 12.2.0.1.0 - Production on Fri Nov 24 10:43:44 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1595035306)
connected to auxiliary database: ORCL (not mounted)
# 执行命令同步主库数据到备库
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;
Starting Duplicate Db at 24-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/home/oracle/app/product/12.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/home/oracle/app/product/12.2.0/dbhome_1/dbs/orapworcl' ;
}
executing Memory Script
Starting backup at 24-NOV-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Finished backup at 24-NOV-23
contents of Memory Script:
{
restore clone from service 'primary' standby controlfile;
}
executing Memory Script
Starting restore at 24-NOV-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/app/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oradata/orcl/control02.ctl
Finished restore at 24-NOV-23
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 clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 12 to new;
set newname for clone datafile 13 to new;
set newname for clone datafile 14 to new;
set newname for clone datafile 15 to new;
restore
from nonsparse from service
'primary' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_temp_%u_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-NOV-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_esb_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_cgyth_te_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_cgyth_de_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00015 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_%u_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 24-NOV-23
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=16 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_system_lp03n66j_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=17 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_esb_lp03nfd1_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_sysaux_lp03ojgj_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=19 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_undotbs1_lp03ozkm_.db f
datafile 5 switched to datafile copy
input datafile copy RECID=20 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_lp03pro8_.db f
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_users_lp03r624_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_cgyth_te_lp03r74f_.db f
datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_cgyth_de_lp03rb96_.db f
datafile 10 switched to datafile copy
input datafile copy RECID=24 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_lp03rscx_.db f
datafile 11 switched to datafile copy
input datafile copy RECID=25 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_lp03t6lz_.db f
datafile 12 switched to datafile copy
input datafile copy RECID=26 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_lp03tzo4_.db f
datafile 13 switched to datafile copy
input datafile copy RECID=27 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_lp03w2sm_.db f
datafile 14 switched to datafile copy
input datafile copy RECID=28 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_lp03ww1j_.db f
datafile 15 switched to datafile copy
input datafile copy RECID=29 STAMP=1153738318 file name=/home/oracle/app/oradata/STANDBY/datafile/o1_mf_hqc_test_lp03xo5v_.db f
Finished Duplicate Db at 24-NOV-23
RMAN> QUIT
Recovery Manager complete.
7.2 rman 常用命令
# 开启实时同步
alter database recover managed standby database using current logfile disconnect from session;
# 关闭实时同步
alter database recover managed standby database cancel;
8. 备库启动
# 执行完同步后,数据库状态会从 nomount--> mount
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
MOUNTED
SQL>
# 然后打开备库，正常启动完成
SQL> alter database open;
# 开启实时同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
# 关闭实时同步
SQL> alter database recover managed standby database cancel;
SQL> select open_mode,log_mode,open_mode ,database_role from v$database;
OPEN_MODE LOG_MODE OPEN_MODE DATABASE_ROLE
-------------------- ------------ -------------------- ----------------
READ ONLY WITH APPLY ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY
# 查看备库进程是否正常
SQL> select process, status, thread#,sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 2338 1 162
RFS IDLE 0 0 0 0
RFS IDLE 1 2339 2262 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 2339 2262 409600
11 rows selected.
SQL>
# 查看主库
SQL> select log_mode,open_mode ,database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ WRITE PRIMARY
SQL>
9. 实时同步验证
9.1 主库执行 sql（记得 commit）
SQL> create table test123 (id number);
Table created.
SQL> insert into test123 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL>
9.2 备库验证 sql
SQL> select * from test123;
no rows selected
SQL> select * from test123;
ID
----------
2
SQL>
10. 排查补充
# V$FLASH_RECOVERY_AREA_USAGE是一个Oracle数据库视图，用于查看闪回恢复区域的使用情况。前两列百分比，后三列为字节。
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG .59 0 3 0
ARCHIVED LOG 81.32 0 501 0
BACKUP PIECE .03 0 3 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG .39 0 2 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oradata/ORCL/controlfile/o1_mf_j4xokbro_.ctl
/home/oracle/app/fast_recovery_area/orcl/ORCL/controlfile/o1_mf_j4xokbsb_.ctl
SQL>
11. 问题
[ORA-00257: Archiver error. Connect AS SYSDBA only until resolved 错误解决_ora00257-CSDN 博客](https://blog.csdn.net/abc_xian/article/details/108391566#:~:text=解决办法：1、登录账号后，查看ORACLE_BASE目录 [oracle%40localhost ~]%24 echo %24ORACLE_BASE%3B%2Forc%2Fapp%2Foracle2、进入数据库操作界面 [oracle%40localhost ~]%24 sqlplus,as sysdba%3B3、查看 flash recovery area 的使用情况 SQL> select * from V%24FLASH_RECOVERY_AR_ora00257)
rman 执行复制报错
[oracle@testserver admin]$ rman target sys/Sunway612@primary auxiliary sys/Sunway612@standby
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Nov 28 20:15:12 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1595035306)
connected to auxiliary database: ORCL (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;
Starting Duplicate Db at 28-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=213 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/home/oracle/app/product/12.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/home/oracle/app/product/12.2.0/dbhome_1/dbs/orapworcl' ;
}
executing Memory Script
Starting backup at 28-NOV-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=422 device type=DISK
Finished backup at 28-NOV-23
contents of Memory Script:
{
sql clone "alter system set control_files =
''/home/oracle/app/oradata/ORCL/controlfile/o1_mf_j4xokbro_.ctl'', ''/home/oracle/app/fast_recovery_area/orcl/ORCL/controlfile/o1_mf_j4xokbsb_.ctl'' comment=
''Set by RMAN'' scope=spfile";
restore clone from service 'primary' standby controlfile;
}
executing Memory Script
sql statement: alter system set control_files = ''/home/oracle/app/oradata/ORCL/controlfile/o1_mf_j4xokbro_.ctl'', ''/home/oracle/app/fast_recovery_area/orcl/ORCL/controlfile/o1_mf_j4xokbsb_.ctl'' comment= ''Set by RMAN'' scope=spfile
Starting restore at 28-NOV-23
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service primary
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/app/oradata/ORCL/controlfile/o1_mf_j4xokbro_.ctl
output file name=/home/oracle/app/fast_recovery_area/orcl/ORCL/controlfile/o1_mf_j4xokbsb_.ctl
Finished restore at 28-NOV-23
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 clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 12 to new;
set newname for clone datafile 13 to new;
restore
from nonsparse from service
'primary' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/app/oradata/STANDBY/datafile/o1_mf_temp_%u_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 28-NOV-23
using channel ORA_AUX_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/28/2023 20:15:30
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 13 found to restore
RMAN-05097: could not validate file header for datafile 13 from service primary
RMAN-06023: no backup or copy of datafile 12 found to restore
RMAN-05097: could not validate file header for datafile 12 from service primary
RMAN-06023: no backup or copy of datafile 11 found to restore
RMAN-05097: could not validate file header for datafile 11 from service primary
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-05097: could not validate file header for datafile 10 from service primary
RMAN-06023: no backup or copy of datafile 9 found to restore
RMAN-05097: could not validate file header for datafile 9 from service primary
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-05097: could not validate file header for datafile 8 from service primary
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-05097: could not validate file header for datafile 5 from service primary
# 解决方法
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1595035306 PARENT 1 26-JAN-17
2 2 ORCL 1595035306 CURRENT 1408558 15-MAR-21
RMAN> reset database to incarnation 2;
12. 参考
oracle DataGuard 不能实时自动同步 - Oracle 数据库管理 - ITPUB 论坛－专业的 IT 技术社区
https://blog.csdn.net/qq942451993/article/details/134231668
[Rman 管理 archivelog 的命令 - 兮兮远远 - 博客园 (cnblogs.com)](https://www.cnblogs.com/lucifa/p/10552797.html#:~:text=RMAN>crosscheck archivelog all%3B -----交叉检查归档日志信息。 RMAN>list expired archivelog all%3B,all%3B -----查看当前归档日志列表信息。 RMAN>list archivelog from time%3D'sysdate-2'%3B -----查看至今两天的日志信息。)
RMAN-06026: 有些目标没有找到 - 终止还原 RMAN-06023: 没有找到数据文件 4 的副本来还原-CSDN 博客
本文由 mdnice 多平台发布
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
