Oracle Data Guard 12c - 从主创建物理备用-之路教程

Oracle Data Guard 12c - 从主创建物理备用

出于本文的目的，以下环境用作示例来描述该过程：

Primary db_name  and db_unique_name: BOSTON
Standby db_name:  BOSTON (same as primary db_name)
Standby db_unique_name:  CHICAGO
Directory:  /u01/app/oracle

注意：我们无法在 PDB（可插入数据库）级别创建物理备用数据库。
它应该在 CDB（容器数据库）级别，例如：整个主 CDB 应该被克隆/创建为物理备用。

https://onitroad.com 更多教程

创建步骤

在备用服务器上创建必要的目录。
创建 pfile 和密码。
在备用服务器上创建静态侦听器条目。
使用 RMAN Duplicate ..from Active Database 克隆主 CDB。
为远程日志传送配置主服务器并在备用服务器上验证 MRP 进度。

$ mkdir -p /u01/app/oracle/admin/chicago/adump
$ mkdir -p /u01/app/oracle/oradata/chicago
$ mkdir -p /u01/app/oracle/fast_recovery_area/chicago
$ mkdir -p /u01/app/oracle/oradata/chicago1/pdbseed
$ mkdir -p /u01/app/oracle/oradata/chicago/db1

注意：像 pdbseed、chicago1 这样的目录特定于 12c。

创建 pfile：

$ echo 'db_name=boston' > /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initchicago.ora

创建密码文件或者从主文件复制：

$ orapwd file=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwchicago password=[password]

做静态监听入口：

$ cd $ORACLE_HOME/network/admin
$ vi listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC=
    (GLOBAL_DBNAME = chicago)
    (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
    (SID_NAME = chicago)
  )
)

重新加载或者重新启动侦听器。

$ lsnrctl stop
$ lsnrctl start

在备侧启动AUX 实例。

$ sqlplus / as sysdba
sql> startup nomount pfile='$ORACLE_HOME/dbs/initchicago.ora';

从活动数据库启动 RMAN DUPLICATE 以创建备用数据库，目标连接到主实例。

$ rman target sys/[password]@boston auxiliary sys/[password]@chicago

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel prim type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database spfile
parameter_value_convert 'boston','chicago'
set db_file_name_convert='boston','chicago'
set log_file_name_convert='boston','chicago'
set log_archive_max_processes='10'
set db_unique_name='chicago'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(boston,chicago)'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=chicago'
set log_Archive_dest_2='service=boston async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=boston'
set log_archive_config='dg_config=(boston,chicago)';
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo1.log'' size 50M";
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo2.log'' size 50M";
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo3.log'' size 50M";
sql channel aux "alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo4.log'' size 50M";
sql channel prim "alter system archive log current";
sql channel aux "alter database recover managed standby database disconnect";
}

输出：

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jan 11 16:11:51 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BOSTON (DBID=2509966093)
connected to auxiliary database: BOSTON (not mounted)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=38 device type=DISK
allocated channel: c2
channel c2: SID=61 device type=DISK
.
.
Starting Duplicate Db at 11-JUL-13

内存脚本的内容：

{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwboston' auxiliary format
 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwchicago'   targetfile
 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileboston.ora' auxiliary format
 '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilechicago.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilechicago.ora''";
}

执行内存脚本：

Starting backup at 11-JUL-13
Finished backup at 11-JUL-13
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilechicago.ora''

内存脚本的内容：

{
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/oracle/admin/chicago/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/oradata/chicago/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/chicago/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers =
 ''(PROTOCOL=TCP) (SERVICE=chicagoXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''boston'', ''chicago'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''boston'', ''chicago'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 10 comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''chicago'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(boston,chicago)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=chicago'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_Archive_dest_2 =
 ''service=boston async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=boston'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}

执行内存脚本：

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/chicago/adump'' comment= '''' scope=spfile
sql statement: alter system set  control_files =  ''/oradata/chicago/control01.ctl'', ''/u01/app/oracle/fast_recovery_area/chicago/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=chicagoXDB)'' comment= '''' scope=spfile
sql statement: alter system set  db_file_name_convert =  ''boston'', ''chicago'' comment= '''' scope=spfile
sql statement: alter system set  log_file_name_convert =  ''boston'', ''chicago'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_max_processes =  10 comment= '''' scope=spfile
sql statement: alter system set  db_unique_name =  ''chicago'' comment= '''' scope=spfile
sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_config =  ''dg_config=(boston,chicago)'' comment= '''' scope=spfile
sql statement: alter system set  log_archive_dest_1 =  ''location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=boston'' comment= '''' scope=spfile
sql statement: alter system set  log_Archive_dest_2 =  ''service=chicago async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=chicago'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config="dg_config=(boston,chicago)" comment=""scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    1043886080 bytes
Fixed Size                     2296280 bytes
Variable Size                696256040 bytes
Database Buffers             339738624 bytes
Redo Buffers                   5595136 bytes
allocated channel: aux
channel aux: SID=19 device type=DISK

内存脚本的内容：

{
   sql clone 'alter database mount standby database';
}

执行内存脚本：

sql statement: alter database mount standby database

内存脚本的内容：

{
   set newname for tempfile  1 to
 "/oradata/chicago/temp01.dbf";
   set newname for tempfile  2 to
 "/oradata/chicago/pdbseed/pdbseed_temp01.dbf";
   set newname for tempfile  3 to
 "/oradata/chicago/chicago1/chicago1_temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/chicago/system01.dbf";
   set newname for datafile  3 to
 "/oradata/chicago/sysaux01.dbf";
   set newname for datafile  4 to
 "/oradata/chicago/undotbs01.dbf";
   set newname for datafile  5 to
 "/oradata/chicago/pdbseed/system01.dbf";
   set newname for datafile  6 to
 "/oradata/chicago/users01.dbf";
   set newname for datafile  7 to
 "/oradata/chicago/pdbseed/sysaux01.dbf";
   set newname for datafile  8 to
 "/oradata/chicago/chicago1/system01.dbf";
   set newname for datafile  9 to
 "/oradata/chicago/chicago1/sysaux01.dbf";
   set newname for datafile  10 to
 "/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf";
   set newname for datafile  11 to
 "/oradata/chicago/chicago1/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/oradata/chicago/system01.dbf"   datafile
 3 auxiliary format
 "/oradata/chicago/sysaux01.dbf"   datafile
 4 auxiliary format
 "/oradata/chicago/undotbs01.dbf"   datafile
 5 auxiliary format
 "/oradata/chicago/pdbseed/system01.dbf"   datafile
 6 auxiliary format
 "/oradata/chicago/users01.dbf"   datafile
 7 auxiliary format
 "/oradata/chicago/pdbseed/sysaux01.dbf"   datafile
 8 auxiliary format
 "/oradata/chicago/chicago1/system01.dbf"   datafile
 9 auxiliary format
 "/oradata/chicago/chicago1/sysaux01.dbf"   datafile
 10 auxiliary format
 "/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf"   datafile
 11 auxiliary format
 "/oradata/chicago/chicago1/example01.dbf"   ;
   sql 'alter system archive log current';
}

执行内存脚本：

executing command: SET NEWNAME
.
.
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/chicago/temp01.dbf in control file
renamed tempfile 2 to /oradata/chicago/pdbseed/pdbseed_temp01.dbf in control file
renamed tempfile 3 to /oradata/chicago/chicago1/chicago1_temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 11-JUL-13
channel c1: starting datafile copy
input datafile file number=00001 name=/oradata/boston/system01.dbf
channel c2: starting datafile copy
input datafile file number=00003 name=/oradata/boston/sysaux01.dbf
channel c3: starting datafile copy
input datafile file number=00009 name=/oradata/boston/boston1/sysaux01.dbf
channel c4: starting datafile copy
input datafile file number=00007 name=/oradata/boston/pdbseed/sysaux01.dbf
channel prim: starting datafile copy
input datafile file number=00011 name=/oradata/boston/boston1/example01.dbf
output file name=/oradata/chicago/chicago1/example01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:02:16
channel prim: starting datafile copy
input datafile file number=00008 name=/oradata/boston/boston1/system01.dbf
output file name=/oradata/chicago/chicago1/sysaux01.dbf tag=TAG20130711T161217
channel c3: datafile copy complete, elapsed time: 00:03:42
channel c3: starting datafile copy
input datafile file number=00005 name=/oradata/boston/pdbseed/system01.dbf
output file name=/oradata/chicago/pdbseed/sysaux01.dbf tag=TAG20130711T161217
channel c4: datafile copy complete, elapsed time: 00:03:43
channel c4: starting datafile copy
input datafile file number=00004 name=/oradata/boston/undotbs01.dbf
output file name=/oradata/chicago/chicago1/system01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:01:52
channel prim: starting datafile copy
input datafile file number=00006 name=/oradata/boston/users01.dbf
output file name=/oradata/chicago/users01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:00:07
channel prim: starting datafile copy
input datafile file number=00010 name=/oradata/boston/boston1/SAMPLE_SCHEMA_users01.dbf
output file name=/oradata/chicago/undotbs01.dbf tag=TAG20130711T161217
channel c4: datafile copy complete, elapsed time: 00:00:47
output file name=/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf tag=TAG20130711T161217
channel prim: datafile copy complete, elapsed time: 00:00:03
output file name=/oradata/chicago/sysaux01.dbf tag=TAG20130711T161217
channel c2: datafile copy complete, elapsed time: 00:04:55
output file name=/oradata/chicago/pdbseed/system01.dbf tag=TAG20130711T161217
channel c3: datafile copy complete, elapsed time: 00:01:12
output file name=/oradata/chicago/system01.dbf tag=TAG20130711T161217
channel c1: datafile copy complete, elapsed time: 00:05:05
Finished backup at 11-JUL-13
sql statement: alter system archive log current

内存脚本的内容：

{
   switch clone datafile all;
}

执行内存脚本：

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=820513044 file name=/oradata/chicago/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=820513044 file name=/oradata/chicago/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=820513044 file name=/oradata/chicago/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=820513044 file name=/oradata/chicago/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=820513044 file name=/oradata/chicago/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=820513044 file name=/oradata/chicago/pdbseed/sysaux01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=13 STAMP=820513044 file name=/oradata/chicago/chicago1/system01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=14 STAMP=820513044 file name=/oradata/chicago/chicago1/sysaux01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=820513045 file name=/oradata/chicago/chicago1/SAMPLE_SCHEMA_users01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=16 STAMP=820513045 file name=/oradata/chicago/chicago1/example01.dbf
Finished Duplicate Db at 11-JUL-13
sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo1.log'' size 50M
sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo2.log'' size 50M
sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo3.log'' size 50M
sql statement: alter database add standby logfile ''/u01/app/oracle/oradata/chicago/stdbyredo4.log'' size 50M
sql statement: alter system archive log current
sql statement: alter database recover managed standby database disconnect
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: prim
released channel: aux
RMAN>
Recovery Manager complete.

准备主数据库以将 REDO 发送到备用数据库。

a.定义 log_archive_config 值。

此参数应在同一 DG 配置中包含所有参与数据库。
假设此参数说明 DG 配置中的主要和所有参与的备用（物理、逻辑、远同步）。

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(boston,Chicago)' SCOPE=BOTH;
System altered.

注意：log_archive_config 值不区分大小写。

b. 设置远程日志传送。

@基本的：

SQL>alter system set log_archive_dest_6='service=chicago async valid_for=(all_logfiles,primary_role) db_unique_name=chicago';
System altered.

注意：这里主要使用 ASYNC redo 传输方法将 REDO 运送到备用。

C.验证重做日志传送，

SQL> alter system switch logfile;
System altered.

SQL> col error for a30
SQL> select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where dest_id=2;
   DEST_ID ERROR                          STATUS    LOG_SEQUENCE APPLIED_SCN
---------- ------------------------------ --------- ------------ ----------
         2                                VALID               63     2133221

验证 Standby 是否正在应用。

注意：在 RMAN 复制后 MRP 已经启动。

SQL> select process,status,thread#,sequence#,blocks from v#managed_standby where process like '%MRP%';
process        status                  thread#          sequence#     blocks
-------------  ----------------------  --------------  -------------  -------
MRP            WAITING_FOR_LOG           1                 64            0

日期：2020-09-17 00:11:27 来源：oir作者：oir

←针对级联备用数据库的 Oracle 12C 增强功能

Oracle Data Guard 配置中客户端连接的基础知识→