G007-ORACLE-INS-DG-01 ORACLE 19C DG 1P1S-CDB Ins ON RHEL 8.2

  • AskScuti

    2020-10-30 18:42:25
  • Oracle
  • 原创
[TOC]

您需要知道的

  • 您需要熟悉如何去搭建 Linux 操作系统，可参考 OS-INS 类相关文档完成此操作
  • 您需要熟悉如何在 Linux 操作系统上安装 Oracle 数据库，可参考 ORACLE-INS-FS 类相关文档完成此操作
  • 本文不适合零基础，如果可以，请按照上述两条进行逐步学习
  • 本文采用的是 ORACLE 19c 版本在红帽 RHEL 8.2 上进行的容器数据库CDB一主一备环境搭建

1 主机环境规划

主库和备库的 DB_NAME 必须一致

规划 主库 备库
数据库版本 19.3.0.0.0 19.3.0.0.0
是否为CDB Yes/CDB Yes/CDB
主机名 henry henrys
IP地址 192.168.1.82 192.168.1.83
DB_UNIQUE_NAME cdb1 cdb1s
DB_NAME cdb1 cdb1
INSTANCE_NAME cdb1 cdb1s
Data Files /oracle/app/oracle/oradata/CDB1 /oracle/app/oracle/oradata/CDB1S
Log Files /oracle/app/oracle/oradata/CDB1 /oracle/app/oracle/oradata/CDB1S
OS RHEL 8.2 RHEL 8.2

2 操作系统安装

  • 主库OS安装，请参考 G005-OS-INS-02 一文，注意主机名及IP设置（主）
  • 备库OS安装，请参考 G005-OS-INS-02 一文，注意主机名及IP设置（备）

3 数据库安装

主备中，主库是完整的数据库软件及数据库；备库仅安装数据库软件即可。

3.1 主库安装

  • 您可参考 G006-ORACLE-INS-SIFS-01 一文来完成主库安装

3.2 备库安装

备库只需完成 1-3 部分的数据库软件安装即可，注意备库主机名、IP及环境变量

  • 您可参考 G006-ORACLE-INS-SIFS-01 一文来完成备库安装
# 备库主机名
[root@henrys ~]# hostname
henrys
# 备库IP地址
[root@henrys ~]# ifconfig
inet 192.168.1.83
# /etc/hosts
[root@henrys ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.83 henrys
# 备库环境变量
[root@henrys scripts]# vim setEnv.sh
export ORACLE_HOSTNAME=henrys
export ORACLE_UNQNAME=CDB1S
export ORACLE_SID=CDB1S

4 Data Guard 搭建

4.1 主库配置

4.1.1 主库强制日志

SQL> alter database force logging;

4.1.2 主库开启归档

SQL> archive log list;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> !mkdir /oracle/arch
SQL> alter system set log_archive_dest_1='location=/oracle/arch';
SQL> alter database open;

4.1.3 主库创建STANDBY日志组

# SQL> set linesize 300
# SQL> col members for 99
# SQL> col member for a55
# SQL> col group# for 99
# SQL> select a.group#,a.sequence#,b.status,a.members,b.member,a.bytes/1024/1024 M from v$log a,v$logfile b where a.group#=b.group# order by a.group#;
SQL> alter system set standby_file_management=manual;
SQL> alter database add standby logfile group 4 '/oracle/app/oracle/oradata/CDB1/redo04.log' size 200M;
SQL> alter database add standby logfile group 5 '/oracle/app/oracle/oradata/CDB1/redo05.log' size 200M;
SQL> alter database add standby logfile group 6 '/oracle/app/oracle/oradata/CDB1/redo06.log' size 200M;
SQL> alter database add standby logfile group 7 '/oracle/app/oracle/oradata/CDB1/redo07.log' size 200M;
SQL> alter system set standby_file_management=auto;
SQL> select group#,type,member from v$logfile order by group#;
GROUP#     TYPE    MEMBER
---------- ------- -------------------------------------------------------
     1 ONLINE  /oracle/app/oracle/oradata/CDB1/redo01.log
     2 ONLINE  /oracle/app/oracle/oradata/CDB1/redo02.log
     3 ONLINE  /oracle/app/oracle/oradata/CDB1/redo03.log
     4 STANDBY /oracle/app/oracle/oradata/CDB1/redo04.log
     5 STANDBY /oracle/app/oracle/oradata/CDB1/redo05.log
     6 STANDBY /oracle/app/oracle/oradata/CDB1/redo06.log
     7 STANDBY /oracle/app/oracle/oradata/CDB1/redo07.log

4.1.4 主库修改参数文件

# 通过当前动态参数文件（SPFILE）创建静态参数文件（PFILE）
# !ls $ORACLE_HOME/dbs/
SQL> create pfile from spfile;
# !ls $ORACLE_HOME/dbs/
# 修改生成的静态参数文件 vim initCDB1.ora
CDB1.__data_transfer_cache_size=0
CDB1.__db_cache_size=1325400064
CDB1.__inmemory_ext_roarea=0
CDB1.__inmemory_ext_rwarea=0
CDB1.__java_pool_size=0
CDB1.__large_pool_size=16777216
CDB1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
CDB1.__pga_aggregate_target=637534208
CDB1.__sga_target=1879048192
CDB1.__shared_io_pool_size=100663296
CDB1.__shared_pool_size=419430400
CDB1.__streams_pool_size=0
CDB1.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/CDB1/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/CDB1/control01.ctl','/oracle/app/oracle/oradata/CDB1/control02.ctl'
*.db_block_size=8192
*.db_name='CDB1'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1XDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='location=/oracle/arch'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=596m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1787m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
# 以下是追加参数 #
DB_NAME=CDB1
DB_UNIQUE_NAME=CDB1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB1,CDB1S)'
CONTROL_FILES='/oracle/app/oracle/oradata/CDB1/control01.ctl','/oracle/app/oracle/oradata/CDB1/control02.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1'
LOG_ARCHIVE_DEST_2='SERVICE=CDB1S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1S'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_CLIENT=CDB1
FAL_SERVER=CDB1S
DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1S','/oracle/app/oracle/oradata/'CDB1''
LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1S','/oracle/app/oracle/oradata/'CDB1''
STANDBY_FILE_MANAGEMENT=AUTO

通过新的静态参数文件PFILE initCDB1.ora 生成新的动态参数文件SPFILE spfileCDB1.ora 并重新启动数据库进行参数加载

SQL> shutdown immediate;
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initCDB1.ora';
SQL> startup

4.1.5 主库配置TNS

# 添加备库连接字符串
[oracle@henry admin]$ vim tnsnames.ora
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )
CDB1S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1S)
    )
  )

4.1.6 主库配置静态监听

[oracle@henry admin]$ vim listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = henry)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = CDB1)
                (ORACLE_HOME = /oracle/app/oracle/product/19.0.0/dbhome_1)
                (SID_NAME = CDB1)
        )
)
# 重启监听
# lsnrctl status
# lsnrctl stop
# lsnrctl start
Services Summary...
Service "CDB1" has 1 instance(s).
  Instance "CDB1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
# 测试连接
[oracle@henry admin]$ sqlplus sys/oracle@cdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 30 11:48:14 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>

4.2 备库配置

4.2.1 备库修改参数文件

# cd $ORACLE_HOME/dbs
# 主备参数进行互换 vim initCDB1S.ora
CDB1S.__data_transfer_cache_size=0
CDB1S.__db_cache_size=1325400064
CDB1S.__inmemory_ext_roarea=0
CDB1S.__inmemory_ext_rwarea=0
CDB1S.__java_pool_size=0
CDB1S.__large_pool_size=16777216
CDB1S.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
CDB1S.__pga_aggregate_target=637534208
CDB1S.__sga_target=1879048192
CDB1S.__shared_io_pool_size=100663296
CDB1S.__shared_pool_size=419430400
CDB1S.__streams_pool_size=0
CDB1S.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/CDB1S/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/CDB1S/control01.ctl','/oracle/app/oracle/oradata/CDB1S/control02.ctl'
*.db_block_size=8192
*.db_name='CDB1'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB1SXDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='location=/oracle/arch'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=596m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1787m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
DB_NAME=CDB1
DB_UNIQUE_NAME=CDB1S
LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB1S,CDB1)'
CONTROL_FILES='/oracle/app/oracle/oradata/CDB1S/control01.ctl','/oracle/app/oracle/oradata/CDB1S/control02.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB1S'
LOG_ARCHIVE_DEST_2='SERVICE=CDB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB1'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_CLIENT=CDB1S
FAL_SERVER=CDB1
DB_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1','/oracle/app/oracle/oradata/CDB1S'
LOG_FILE_NAME_CONVERT='/oracle/app/oracle/oradata/CDB1','/oracle/app/oracle/oradata/CDB1S'
STANDBY_FILE_MANAGEMENT=AUTO
# 备库创建对应目录
mkdir -p /oracle/app/oracle/admin/CDB1S/adump
mkdir -p /oracle/app/oracle/oradata/CDB1S
mkdir /oracle/arch

4.2.2 备库配置TNS

# 进入备库目录
cd $ORACLE_HOME/network/admin
# 拷贝主库文件至备库
scp root@192.168.1.82:/oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora .
The authenticity of host '192.168.1.82 (192.168.1.82)' can't be established.
ECDSA key fingerprint is SHA256:IsxJz8PQu+9spn5xbVGzZkkqJ36x3AJIkYqW7ECxbW8.
Are you sure you want to continue connecting (yes/no/[fingerprint])?  yes
Warning: Permanently added '192.168.1.82' (ECDSA) to the list of known hosts.
root@192.168.1.82's password:
tnsnames.ora                           100%  355   235.7KB/s   00:00
[oracle@henrys admin]$ cat tnsnames.ora
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )
CDB1S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1S)
    )
  )
# 尝试在备库通过连接字符串登录主库
[oracle@henrys admin]$ sqlplus sys/oracle@cdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 30 12:10:44 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>

4.2.3 备库配置监听


# 进入备库目录
cd $ORACLE_HOME/network/admin
# 拷贝主库文件至备库
scp root@192.168.1.82:/oracle/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora .
# 注意备库中的 （对应的主机IP、GLOBAL_DBNAME、ORACLE_HOME和SID_NAME） vim listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
        (SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = CDB1S)
                (ORACLE_HOME = /oracle/app/oracle/product/19.0.0/dbhome_1)
                (SID_NAME = CDB1S)
        )
)

4.2.4 备库配置密码文件

# 进入备库目录
[oracle@henrys admin]$ cd $ORACLE_HOME/dbs/
# 拷贝主库密码文件至备库
[oracle@henrys dbs]$ scp root@192.168.1.82:/oracle/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCDB1 .
# 重命名备库密码文件
[oracle@henrys dbs]$ mv orapwCDB1 orapwCDB1S

4.2.5 备库创建动态参数文件

  • 备库重启监听，测试TNS连接字符串
lsnrctl stop
lsnrctl start
Services Summary...
Service "CDB1S" has 1 instance(s).
  Instance "CDB1S", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@henrys ~]$ sqlplus sys/oracle@cdb1s as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 30 12:24:48 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>
  • 备库创建动态参数文件
SQL> !ls $ORACLE_HOME/dbs
initCDB1S.ora  init.ora  orapwCDB1S
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initCDB1S.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1879046112 bytes
Fixed Size            9136096 bytes
Variable Size          436207616 bytes
Database Buffers     1426063360 bytes
Redo Buffers            7639040 bytes
SQL>

4.3 DG 创建物理备库

4.3.1 通过 RMAN 备份方式创建 DG 备库

  • 主库进行 RMAN 全备
mkdir /oracle/bak
rman target /
RMAN> show all;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/bak/%F.ctl';
RMAN> backup database format '/oracle/bak/%s_%d_%U.full';
  • 主库备份文件拷贝至备库相同目录中
scp root@192.168.1.82:/oracle/bak/* .
  • 通过 RMAN 还原备库（命令可在主库执行，也可在备库执行）
RMAN> rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb1s
RMAN> duplicate target database for standby;

4.3.2 通过 Active 复制技术创建 DG 备库

  • 命令可在主库执行，也可在备库执行
RMAN> rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb1s
RMAN> duplicate target database for standby from active database;
  • 本次采用后者方式创建，过程记录如下
[oracle@henrys ~]$ rman target sys/oracle@cdb1 auxiliary sys/oracle@cdb1s
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Oct 30 13:00:10 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: CDB1 (DBID=1028799411)
connected to auxiliary database: CDB1 (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 30-OCT-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/oracle/app/oracle/product/19.0.0/dbhome_1/dbs/orapwCDB1S'   ;
}
executing Memory Script
Starting backup at 30-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
Finished backup at 30-OCT-20
contents of Memory Script:
{
   restore clone from service  'cdb1' standby controlfile;
}
executing Memory Script
Starting restore at 30-OCT-20
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 cdb1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/app/oracle/oradata/CDB1S/control01.ctl
output file name=/oracle/app/oracle/oradata/CDB1S/control02.ctl
Finished restore at 30-OCT-20
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 tempfile  1 to 
 "/oracle/app/oracle/oradata/CDB1S/temp01.dbf";
   set newname for tempfile  2 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/temp012020-10-29_14-22-15-414-PM.dbf";
   set newname for tempfile  3 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/oracle/app/oracle/oradata/CDB1S/system01.dbf";
   set newname for datafile  3 to 
 "/oracle/app/oracle/oradata/CDB1S/sysaux01.dbf";
   set newname for datafile  4 to 
 "/oracle/app/oracle/oradata/CDB1S/undotbs01.dbf";
   set newname for datafile  5 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/system01.dbf";
   set newname for datafile  6 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to 
 "/oracle/app/oracle/oradata/CDB1S/users01.dbf";
   set newname for datafile  8 to 
 "/oracle/app/oracle/oradata/CDB1S/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/system01.dbf";
   set newname for datafile  10 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/sysaux01.dbf";
   set newname for datafile  11 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/undotbs01.dbf";
   set newname for datafile  12 to 
 "/oracle/app/oracle/oradata/CDB1S/PDB1/users01.dbf";
   restore
   from  nonsparse   from service 
 'cdb1'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/CDB1S/temp01.dbf in control file
renamed tempfile 2 to /oracle/app/oracle/oradata/CDB1S/pdbseed/temp012020-10-29_14-22-15-414-PM.dbf in control file
renamed tempfile 3 to /oracle/app/oracle/oradata/CDB1S/PDB1/temp01.dbf 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
Starting restore at 30-OCT-20
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 cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/CDB1S/system01.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 cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/CDB1S/sysaux01.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 cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/CDB1S/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/app/oracle/oradata/CDB1S/pdbseed/system01.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 cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /oracle/app/oracle/oradata/CDB1S/pdbseed/sysaux01.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 cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oracle/app/oracle/oradata/CDB1S/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /oracle/app/oracle/oradata/CDB1S/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /oracle/app/oracle/oradata/CDB1S/PDB1/system01.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 cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /oracle/app/oracle/oradata/CDB1S/PDB1/sysaux01.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 cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /oracle/app/oracle/oradata/CDB1S/PDB1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /oracle/app/oracle/oradata/CDB1S/PDB1/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-OCT-20
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=4 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1055163710 file name=/oracle/app/oracle/oradata/CDB1S/PDB1/users01.dbf
Finished Duplicate Db at 30-OCT-20

5 主备同步测试

5.1 备库开启应用

备库默认状态为 MOUNT，开启同步应用后，仅同步应用日志，无法提供实时业务数据查询

  • 确认主备环境
# 主库状态查询
SQL> select database_role,open_mode,protection_mode from v$database;
DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE
---------------- -------------------- --------------------
PRIMARY          READ WRITE           MAXIMUM PERFORMANCE
# 备库状态查询
SQL> select database_role,open_mode,protection_mode from v$database;
DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED              MAXIMUM PERFORMANCE
  • 备库开启应用
SQL> alter database recover managed standby database disconnect from session;
  • 主库切日志并查询当前最新归档
SQL> alter system switch logfile;
System altered.
SQL> select unique thread#,max(sequence#) over(partition by thread#) last from v$archived_log;
   THREAD#     LAST
---------- ----------
     1           18
  • 备库查询当前最新归档
SQL> select unique thread#,max(sequence#) over(partition by thread#) last from v$archived_log;
   THREAD#     LAST
---------- ----------
     1           18

5.2 备库开启ADG

ADG （Active Data Guard）作为独立产品功能包含在 EE 企业版本中，其它版本不支持。备库开启 ADG 功能后，可进行同步查询。

  • 备库取消同步
SQL> alter database recover managed standby database cancel;
  • 备库开启 ADG
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
  • 备库打开 PDB1 数据库
SQL> alter pluggable database pdb1 open;
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2     PDB$SEED                       READ ONLY  NO
     3     PDB1                           READ ONLY  NO
  • 主库 PDB1 插入测试数据
SQL> alter session set container=pdb1;
Session altered.
SQL> create table askscuti(id number);
Table created.
SQL> insert into askscuti values(1);
1 row created.
SQL> commit;
Commit complete.
  • 备库 PDB1 查询
SQL> alter session set container=pdb1;
Session altered.
SQL> select * from askscuti;
    ID
----------
     1

5.3 关于实时应用的说明

2.5.6.5 Real-Time Apply is Default Setting for Data Guard
In previous releases, when creating a Data Guard configuration using the SQL command line, the default configuration was to apply redo from archived log files on the standby database. In Oracle Database 12c Release 1 (12.1), the default configuration is to use real-time apply so that redo is applied directly from the standby redo log file.

  • 在12c之前版本中，MRP的默认配置是对备用数据库上的归档日志文件进行应用重做。从Oracle数据库12c Release 1（12.1）开始，默认配置是使用实时应用（real-time apply），直接从备用重做日志文件应用重做。当前版本为19c，备库开启同步后，默认为实时应用。

  • 备库开启应用语句

SQL> alter database recover managed standby database disconnect;(应用在线日志实时同步)
SQL> alter database recover managed standby database using archived logfile disconnect;（应用归档日志非实时同步）
# 查询当前备库是否为实时应用（MANAGED REAL TIME APPLY 代表实时应用）
SQL> select dest_name,status,recovery_mode from v$archive_dest_status;
DEST_NAME            STATUS    RECOVERY_MODE
-------------------- --------- ----------------------------------
LOG_ARCHIVE_DEST_1   VALID     MANAGED REAL TIME APPLY
  • 12c之前版本中，如11g开启实时应用，需执行以下命令
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

[End]

请使用浏览器的分享功能分享到微信等