Oracle 11g RAC to 11g RAC ADG部署搭建

一、目标端软件环境准备

操作系统：CentOS Linux release 6.8
数据库：oracle 11.2.0.4 R2 RAC
只需要安装grid软件和oracle软件即可

二、手工创建磁盘组

GI、ORACLE软件安装后，需要创建磁盘组安装DB，磁盘组名称和源端保持一致

三、rac adg部署

1.DG基础环境
主库rac环境节点一: 192.168.20.15 crm-scan | db_name=orcldb db_unique_name=orcldb service_names=orcldb |主机名：db1
备库rac环境节点一：192.168.20.56 bossdb-scan | db_name=orcldb db_unique_name=orcldbst service_names=orcldb |主机名：db51

2.主库开启强制归档以及开启归档模，以及修改主库初始化参数
开启强制归档
alter database force logging;
select force_logging from v$database;

3.主库配置归档删除策略
$ rman target /
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
RMAN> show all;

RMAN configuration parameters for database with db_unique_name NXDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_NXDB1.f'; # default

RMAN>

4.主库添加standby redo logfile

添加日志规则：
如果节点RAC主库每个thread redo log有N组，standby redo log则需要standbby redo log组数为(n+1)*thread组。
主库查询redo 日志组数，有2个thread，每个thread有4组日志，standby则需要10组

===================================================================
#查看 Online redo log 大小及位置：
SQL> select group#,thread#,members,bytes from v$log;

GROUP# THREAD# MEMBERS BYTES
---------- ---------- ---------- ----------
1 1 1 52428800
2 1 1 52428800
3 2 1 52428800
4 2 1 52428800

SQL> select group#,member from v$logfile;
----GROUP#----------MEMBER--------------------------------------
2 +DATA01/NXDB/redo02.log
1 +DATA01/NXDB/redo01.log
3 +DATA01/NXDB/redo03.log
4 +DATA01/NXDB/redo04.log

#添加Standby redo log
alter database add standby logfile thread 1 group 5 '+DATA01/NXDB/redo05_1.log' size 50M;
alter database add standby logfile thread 1 group 6 '+DATA01/NXDB/redo06_1.log' size 50M;
alter database add standby logfile thread 1 group 7 '+DATA01/NXDB/redo07_1.log' size 50M;
alter database add standby logfile thread 2 group 8 '+DATA01/NXDB/redo08_1.log' size 50M;
alter database add standby logfile thread 2 group 9 '+DATA01/NXDB/redo09_1.log' size 50M;
alter database add standby logfile thread 2 group 10 '+DATA01/NXDB/redo10_1.log' size 50M;
#检查确认
SQL> select group#,thread#,bytes/1024/1024 Size_MB from v$log;

GROUP# THREAD# SIZE_MB
---------- ---------- ----------
1 1 50
2 1 50
3 2 50
4 2 50

SQL> select group#,thread#,bytes/1024/1024 size_Mb from v$standby_log;

GROUP# THREAD# SIZE_MB
---------- ---------- ----------
5 1 50
6 1 50
7 1 50
8 2 50
9 2 50
10 2 50

6 rows selected.

5.主库和备库监听配置以及TNS配置
以oracle用户在主库所有节点上配置 tnsname.ora
vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

NXDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NXDB1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NXDB)
)
)

NXYLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NXYLDB1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NXDB)
)
)

将主库的tnsnames.ora文件拷贝到备库的所有节点，将修改如下，将名称替换下
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

scp tnsnames.ora NXYLDB1:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
scp tnsnames.ora NXYLDB2:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

登陆备库查看效果如下：
[oracle@NXDB2 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

NXYLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NXDB1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NXYLDB)
)
)

NXDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NXYLDB1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NXDB)
)
)

6.配置主库 ADG 参数
1、备份spfile文件
create pfile=’/u01/initbak20211013.ora’ from spfile;

2、配置主库参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(NXDB,NXYLDB)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+data01/NXDB/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=NXDB' scope=both;
alter system set log_archive_dest_2='SERVICE=NXYLDB SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=NXYLDB' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both;
alter system set LOG_FILE_NAME_CONVERT='+DATA01/NXDB','+DATA01/NXDB' scope=spfile;
alter system set DB_FILE_NAME_CONVERT='+DATA01/NXDB','+DATA01/NXDB' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both;
alter system set FAL_SERVER='NXYLDB' scope=both;

3、重启数据库
srvctl stop database -d NXDB
srvctl start database -d NXDB
这里不能按照单实例轮询重启，必须所有节点都关闭重启。

7.配置密码文件
1、将主库1 节点的密码文件拷贝到主库的所有节点和备库上：
[oracle@NXDB1 ~]$ cd $ORACLE_HOME/dbs
[oracle@NXDB1 dbs]$ scp orapwNXDB1 NXYLDB1:$ORACLE_HOME/dbs/orapwNXYLDB1
[oracle@NXDB1 dbs]$ scp orapwNXDB1 NXYLDB2:$ORACLE_HOME/dbs/orapwNXYLDB2

8.创建备库初始化参数文件
1、在主库上生成最新 pfile 文件，并拷贝到备库上，并重命名为 initNXYLDB.ora
create pfile='/tmp/initNXDB.ora' from spfile;
scp /tmp/initNXDB.ora NXYLDB1:/tmp/initNXYLDB.ora

2、修改备库上的 pfile 文件
[oracle@NXDB1 ~]$ more /tmp/initNXDB.ora
NXDB2.__db_cache_size=243269632
NXDB1.__db_cache_size=251658240
NXDB1.__java_pool_size=4194304
NXDB2.__java_pool_size=4194304
NXDB1.__large_pool_size=8388608
NXDB2.__large_pool_size=8388608
NXDB1.__pga_aggregate_target=281018368
NXDB2.__pga_aggregate_target=297795584
NXDB1.__sga_target=503316480
NXDB2.__sga_target=486539264
NXDB1.__shared_io_pool_size=0
NXDB2.__shared_io_pool_size=0
NXDB2.__shared_pool_size=218103808
NXDB1.__shared_pool_size=226492416
NXDB1.__streams_pool_size=0
NXDB2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/NXDB/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA01/NXYLDB/control01.ctl','+DATA01/NXYLDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA01/NXDB','+DATA01/NXYLDB'
*.db_name='NXDB'
*.service_names='NXDB'
*.db_unique_name='NXYLDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=NXDBXDB)'
*.fal_server='NXDB'
NXDB2.instance_number=2
NXDB1.instance_number=1
*.log_archive_config='DG_CONFIG=(NXYLDB,NXDB)'
*.log_archive_dest_1='LOCATION=+data01/NXYLDB/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=NXYLDB'
*.log_archive_dest_2='SERVICE=NXDB SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=NXDB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA01/NXDB','+DATA01/NXYLDB'
*.memory_target=783286272
*.open_cursors=300
*.processes=150
*.remote_listener='NXDB-scan:1521'
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
NXDB2.thread=2
NXDB1.thread=1
NXDB1.undo_tablespace='UNDOTBS1'
NXDB2.undo_tablespace='UNDOTBS2'
[oracle@NXDB1 ~]$

3、和主库对比修改了哪些地方，主库配置如下所示：
NXDB2.__db_cache_size=260046848
NXDB1.__db_cache_size=289406976
NXDB2.__java_pool_size=4194304
NXDB1.__java_pool_size=4194304
NXDB2.__large_pool_size=8388608
NXDB1.__large_pool_size=8388608
NXDB2.__pga_aggregate_target=318767104
NXDB1.__pga_aggregate_target=276824064
NXDB2.__sga_target=465567744
NXDB1.__sga_target=507510784
NXDB2.__shared_io_pool_size=0
NXDB1.__shared_io_pool_size=0
NXDB2.__shared_pool_size=180355072
NXDB1.__shared_pool_size=192937984
NXDB2.__streams_pool_size=0
NXDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/NXDB/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA01/NXDB/control01.ctl','+DATA01/NXDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA01/NXYLDB','+DATA01/NXDB'
*.db_name='NXDB'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=NXDBXDB)'
*.fal_server='NXYLDB'
db_unique_name=NXYLDB
NXDB2.instance_number=2
NXDB1.instance_number=1
*.log_archive_config='DG_CONFIG=(NXDB,NXYLDB)'
*.log_archive_dest_1='LOCATION=+data01/NXDB/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=NXDB'
*.log_archive_dest_2='SERVICE=NXYLDB SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=NXYLDB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA01/NXYLDB','+DATA01/NXDB'
*.memory_target=783286272
*.open_cursors=300
*.processes=150
*.remote_listener='NXDB-scan:1521'
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
NXDB2.thread=2
NXDB1.thread=1
NXDB1.undo_tablespace='UNDOTBS1'
NXDB2.undo_tablespace='UNDOTBS2'

9.创建备库必要目录
1、查看路径
[oracle@NXDB1 ~]$ cat /tmp/initNXDB.ora | grep /
*.audit_file_dest='/u01/app/oracle/admin/NXYLDB/adump'
*.control_files='+DATA01/NXYLDB/control01.ctl','+DATA01/NXYLDB/control02.ctl'
*.db_file_name_convert='+DATA01/NXDB','+DATA01/NXYLDB'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='LOCATION=+data01/NXYLDB/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=NXYLDB'
*.log_file_name_convert='+DATA01/NXDB','+DATA01/NXYLDB'

2、在备库主机上创建需要的数据库目录
[oracle@NXDB2 ~]$ mkdir -p /u01/app/oracle/admin/NXYLDB/adump
[oracle@NXDB2 ~]$ mkdir -p /u01/app/oracle/admin/NXYLDB/dpdump
[oracle@NXDB2 ~]$ mkdir -p /u01/app/oracle/admin/NXYLDB/hdump
[oracle@NXDB2 ~]$ mkdir -p /u01/app/oracle/admin/NXYLDB/pfile
$ su – grid
$ asmcmd
ASMCMD> +DATA01/NXYLDB
ASMCMD> mkdir +DATA01/NXYLDB
ASMCMD> mkdir +data01/NXYLDB/archive
ASMCMD> exit

10.备库 LISTENER 配置
1、grid用户修改listener.ora文件，备库两个节点都需要修改，这里以节点1为例
[grid@NXDB1 admin]$ more /u02/app/oracle/product/11.2.0/grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME= PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = NXDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=NXDB1)
)
)
[grid@NXDB1 admin]$

2、重启监听
[grid@NXDB1 admin]$ srvctl start listener -l LISTENER

11.备库 ADG 初始化
1、启动备库到nomount状态
SQL> startup nomount pfile='/tmp/initNXDB.ora';

2、主库和备库测试连接
[oracle@NXDB2 ~]$ sqlplus sys/admin123@NXYLDB as sysdba
[oracle@NXDB2 ~]$ sqlplus sys/admin123@NXDB as sysdba

3、将数据库注册到集群中，1节点执行
[oracle@NXDB1 ~]$ srvctl add database -d NXYLDB -o /u01/app/oracle/product/11.2.0/dbhome_1
[oracle@NXDB1 ~]$ srvctl add instance -d NXYLDB -i NXYLDB1 -n NXDB1
[oracle@NXDB1 ~]$ srvctl add instance -d NXYLDB -i NXYLDB2 -n NXDB2

4、备库通过 RMAN duplicate 完成进行备库初始化同步
[oracle@NXDB1 ~]$ rman target sys/admin123@NXDB auxiliary sys/admin123@NXYLDB

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 17 22:39:23 2021

connected to target database: NXDB (DBID=680083408)
connected to auxiliary database: NXDB (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

Starting Duplicate Db at 17-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 instance=NXDB1 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwNXDB1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwNXDB1' ;
}
executing Memory Script

Starting backup at 17-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=NXDB1 device type=DISK
Finished backup at 17-OCT-21

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA01/NXYLDB/control01.ctl';
restore clone controlfile to '+DATA01/NXYLDB/control02.ctl' from
'+DATA01/NXYLDB/control01.ctl';
}
executing Memory Script

Starting backup at 17-OCT-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_NXDB1.f tag=TAG20211017T223931 RECID=5 STAMP=1086215978
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 17-OCT-21

Starting restore at 17-OCT-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-OCT-21

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
"+DATA01/NXYLDB/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"+DATA01/NXYLDB/system01.dbf";
set newname for datafile 2 to
"+DATA01/NXYLDB/sysaux01.dbf";
set newname for datafile 3 to
"+DATA01/NXYLDB/undotbs01.dbf";
set newname for datafile 4 to
"+DATA01/NXYLDB/users01.dbf";
set newname for datafile 5 to
"+DATA01/NXYLDB/undotbs02.dbf";
set newname for datafile 6 to
"+DATA01/NXYLDB/data01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+DATA01/NXYLDB/system01.dbf" datafile
2 auxiliary format
"+DATA01/NXYLDB/sysaux01.dbf" datafile
3 auxiliary format
"+DATA01/NXYLDB/undotbs01.dbf" datafile
4 auxiliary format
"+DATA01/NXYLDB/users01.dbf" datafile
5 auxiliary format
"+DATA01/NXYLDB/undotbs02.dbf" datafile
6 auxiliary format
"+DATA01/NXYLDB/data01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA01/NXYLDB/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 17-OCT-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA01/NXDB/system01.dbf
output file name=+DATA01/NXYLDB/system01.dbf tag=TAG20211017T224006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA01/NXDB/sysaux01.dbf
output file name=+DATA01/NXYLDB/sysaux01.dbf tag=TAG20211017T224006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA01/NXDB/data01.dbf
output file name=+DATA01/NXYLDB/data01.dbf tag=TAG20211017T224006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA01/NXDB/undotbs01.dbf
output file name=+DATA01/NXYLDB/undotbs01.dbf tag=TAG20211017T224006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA01/NXDB/undotbs02.dbf
output file name=+DATA01/NXYLDB/undotbs02.dbf tag=TAG20211017T224006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA01/NXDB/users01.dbf
output file name=+DATA01/NXYLDB/users01.dbf tag=TAG20211017T224006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-OCT-21

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "+DATA01/NXDB/archive/2_22_1085768275.dbf" auxiliary format
"+DATA01/NXYLDB/archive/2_22_1085768275.dbf" archivelog like
"+DATA01/NXDB/archive/2_23_1085768275.dbf" auxiliary format
"+DATA01/NXYLDB/archive/2_23_1085768275.dbf" archivelog like
"+DATA01/NXDB/archive/1_26_1085768275.dbf" auxiliary format
"+DATA01/NXYLDB/archive/1_26_1085768275.dbf" archivelog like
"+DATA01/NXDB/archive/1_27_1085768275.dbf" auxiliary format
"+DATA01/NXYLDB/archive/1_27_1085768275.dbf" archivelog like
"+DATA01/NXDB/archive/2_21_1085768275.dbf" auxiliary format
"+DATA01/NXYLDB/archive/2_21_1085768275.dbf" ;
catalog clone archivelog "+DATA01/NXYLDB/archive/2_22_1085768275.dbf";
catalog clone archivelog "+DATA01/NXYLDB/archive/2_23_1085768275.dbf";
catalog clone archivelog "+DATA01/NXYLDB/archive/1_26_1085768275.dbf";
catalog clone archivelog "+DATA01/NXYLDB/archive/1_27_1085768275.dbf";
catalog clone archivelog "+DATA01/NXYLDB/archive/2_21_1085768275.dbf";
switch clone datafile all;
}
executing Memory Script

Starting backup at 17-OCT-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=22 RECID=43 STAMP=1086216008
output file name=+DATA01/NXYLDB/archive/2_22_1085768275.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=23 RECID=45 STAMP=1086216122
output file name=+DATA01/NXYLDB/archive/2_23_1085768275.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=26 RECID=44 STAMP=1086216010
output file name=+DATA01/NXYLDB/archive/1_26_1085768275.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=27 RECID=46 STAMP=1086216124
output file name=+DATA01/NXYLDB/archive/1_27_1085768275.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=21 RECID=42 STAMP=1086213932
output file name=+DATA01/NXYLDB/archive/2_21_1085768275.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07
Finished backup at 17-OCT-21

cataloged archived log
archived log file name=+DATA01/NXYLDB/archive/2_22_1085768275.dbf RECID=1 STAMP=1086216148

cataloged archived log
archived log file name=+DATA01/NXYLDB/archive/2_23_1085768275.dbf RECID=2 STAMP=1086216148

cataloged archived log
archived log file name=+DATA01/NXYLDB/archive/1_26_1085768275.dbf RECID=3 STAMP=1086216148

cataloged archived log
archived log file name=+DATA01/NXYLDB/archive/1_27_1085768275.dbf RECID=4 STAMP=1086216148

cataloged archived log
archived log file name=+DATA01/NXYLDB/archive/2_21_1085768275.dbf RECID=5 STAMP=1086216148

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1086216149 file name=+DATA01/NXYLDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1086216149 file name=+DATA01/NXYLDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1086216149 file name=+DATA01/NXYLDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1086216149 file name=+DATA01/NXYLDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1086216149 file name=+DATA01/NXYLDB/undotbs02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=1086216149 file name=+DATA01/NXYLDB/data01.dbf

contents of Memory Script:
{
set until scn 1704676;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-OCT-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 26 is already on disk as file +DATA01/NXYLDB/archive/1_26_1085768275.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA01/NXYLDB/archive/1_27_1085768275.dbf
archived log for thread 2 with sequence 22 is already on disk as file +DATA01/NXYLDB/archive/2_22_1085768275.dbf
archived log for thread 2 with sequence 23 is already on disk as file +DATA01/NXYLDB/archive/2_23_1085768275.dbf
archived log file name=+DATA01/NXYLDB/archive/1_26_1085768275.dbf thread=1 sequence=26
archived log file name=+DATA01/NXYLDB/archive/2_22_1085768275.dbf thread=2 sequence=22
archived log file name=+DATA01/NXYLDB/archive/1_27_1085768275.dbf thread=1 sequence=27
archived log file name=+DATA01/NXYLDB/archive/2_23_1085768275.dbf thread=2 sequence=23
media recovery complete, elapsed time: 00:00:05
Finished recover at 17-OCT-21
Finished Duplicate Db at 17-OCT-21

RMAN> exit

Recovery Manager complete.
[oracle@NXDB1 ~]$

5、在线复制完成之后，打开备库
#打开数据库
SQL> alter database open;

Database altered.
#查看状态
SQL> select name,db_unique_name,open_mode from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE
--------- ------------------------------ --------------------
NXDB NXYLDB READ ONLY

SQL>
启动 apply应用日志
SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.
#确认状态
SQL> select name,db_unique_name,open_mode from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE
--------- ------------------------------ --------------------
NXDB NXYLDB READ ONLY WITH APPLY

12.启动另外一个节点
1、使用pfile生成spfile
SQL> create spfile='+DATA01/NXYLDB/spfileNXYLDB.ora' from pfile='/tmp/initNXDB.ora' ;

2、创建pfile，内容如下，并将节点1的pfile 拷贝到节点2：
[oracle@NXDB1 ~]$ echo "SPFILE='+DATA01/NXYLDB/spfileNXYLDB.ora'" > $ORACLE_HOME/dbs/initNXYLDB1.ora'
[oracle@NXDB1 ~]$ scp $ORACLE_HOME/dbs/initNXYLDB1.ora NXDB2:$ORACLE_HOME/dbs/initNXYLDB2.ora

3、重启数据库
[grid@NXDB2 ~]$ srvctl stop database -d NXYLDB
[grid@NXDB2 ~]$ srvctl start database -d NXYLDB

4、开启实时日志应用
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.

5、查看数据库状态
#主库
SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
NXDB NXDB READ WRITE PRIMARY

#备库
SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
NXDB NXYLDB READ ONLY PHYSICAL STANDBY

13.测试adg同步是否正常
1、主库测试建表空间，建用户，建表、插入数据。
SQL> create tablespace adg datafile '+data01/NXDB/adg.dbf' size 50m autoextend on next 100m maxsize unlimited;
Tablespace created.

SQL> create user adg identified by adg default tablespace adg;
User created.

SQL> grant dba to adg;

Grant succeeded.

SQL> create table adg.test01 (id number , name varchar(20));

Table created.

SQL> insert into adg.test01 values(2,'xxxxx');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from adg.test01;

ID NAME
---------- --------------------
2 xxxxx

SQL>

2、备库查看所建的表空间、用户、表、数据

SQL> select * from adg.test01;

ID NAME
---------- --------------------
2 xxxxx

SQL>

3、查看 DataGuard 相关进程信息

#主库
SQL> /

SQL>

#备库

SQL> SELECT PID,PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID, THREAD# , SEQUENCE# SEQ#,BLOCK# , BLOCKS FROM V$MANAGED_STANDBY;

PID PROCESS STATUS CLIENT_P CLIENT_PID THREAD# SEQ# BLOCK# BLOCKS
-------- --------- ------------ -------- ---------------------------------------- ------- ---------- ---------- ----------
9915 ARCH CLOSING ARCH 9915 1 24 1 945
9917 ARCH CONNECTED ARCH 9917 0 0 0 0
9919 ARCH CONNECTED ARCH 9919 0 0 0 0
9921 ARCH CLOSING ARCH 9921 2 19 1 1717
9970 RFS IDLE ARCH 23651 0 0 0 0
9995 RFS IDLE ARCH 23061 0 0 0 0
9935 RFS IDLE LGWR 22917 2 20 7574 1
9942 RFS IDLE LGWR 23477 1 25 7574 1
9948 RFS IDLE UNKNOWN 23057 0 0 0 0
9950 RFS IDLE UNKNOWN 23647 0 0 0 0
12166 MRP0 APPLYING_LOG N/A N/A 1 25 7573 102400

11 rows selected.

14.清除恢复备库的静态注册文件
[grid@NXDB1 ~]$ more /u02/app/oracle/product/11.2.0/grid/network/admin/listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

#SID_LIST_LISTENER =
# (SID_LIST =
# (SID_DESC =
# (SID_NAME= PLSExtProc)
# (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
# (PROGRAM = extproc)
# )
# (SID_DESC =
# (GLOBAL_DBNAME = NXDB)
# (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
# (SID_NAME=NXDB1)
# )
# )
[grid@NXDB1 ~]$

四、主备切换：
注：rac to rac的adg切换只需在其中一个节点执行相应命令即可，执行完后，再把另外的节点open
1、在做切换时，主库需要将除了 1 节点之外的所有节点全部关闭：
[grid@NXDB1 ~]$ srvctl stop instance -d NXDB -i NXDB2

2、在主库上执行如下操作，将主库切换为备库角色
#查看switchover 状态
SQL> select open_mode, switchover_status from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
NXDB PRIMARY TO STANDBY

#将主库切换为备库角色
SQL> alter database commit to switchover to physical standby;

Database altered.

当 SWITCHOVER_STATUS 值为 TO SYANDBY 时，表示主库可以切换为备库角色；
如果主库的 SWITCHOVER_STATUS 值为 SESSIONS ACTIVE，则表示当前有回话正在连接数据库，则可以执行如下操作完成主备角色切换：
alter database commit to switchover to physical standby with session shutdown;

3、在备库上执行如下操作，将备库转换为主库角色
#查看备库状态
SQL> select open_mode, switchover_status from v$database;

OPEN_MODE SWITCHOVER_STATUS
-------------------- --------------------
READ ONLY WITH APPLY TO PRIMARY

#切换成主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

4、检查备库
#如果 STATUS 显示为非 OPEN，则执行如下操作，启动数据库
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
NXDB1 MOUNTED

[root@NXDB2 ~]# su - grid
[grid@NXDB2 ~]$ srvctl stop database -d NXDB
[grid@NXDB2 ~]$ srvctl start database -d NXDB

#查看数据库状态，模式已经变成主库
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
NXDB1 OPEN

SQL> select name,database_role,switchover_status from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
NXDB PRIMARY FAILED DESTINATION

5、启动备库（原先的主库）
#启动数据库
[grid@NXDB2 ~]$ srvctl start database -d NXDB
#查看数据库模式，此时主库已经切换为物理备库
SQL> select name,database_role,switchover_status from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
NXDB PHYSICAL STANDBY RECOVERY NEEDED

SQL>
SQL> select open_mode, switchover_status from v$database;

OPEN_MODE SWITCHOVER_STATUS
-------------------- --------------------
READ ONLY RECOVERY NEEDED

SQL>

#启动应用日志
SQL> alter database recover managed standby database disconnect from session using current logfile;

#查看主库状态
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
NXDB PRIMARY TO STANDBY

SQL>
#查看备库状态
SQL> select open_mode, switchover_status from v$database;

OPEN_MODE SWITCHOVER_STATUS
-------------------- --------------------
READ ONLY WITH APPLY NOT ALLOWED

SQL>