oracle 11g rac adg 搭建实施
斜宁
一、环境说明
开始配置 Oracle Active Data Gurad 之前， 需要事先准备好相应的环境， 根据规划安装主备库的数据库环境，备库只需安装数据库软件，不需要创建实例，本次实施环境全程通过vmware workstation搭建的虚拟机环境进行的测试,，先搭建一个rac gi主库环境，不建库，然后通过虚拟机克隆的方式生成备库的集群环境，再在主库上建库，配置adg的环境。
1、基础环境
| 事项 | 主库 | 备库 |
|---|---|---|
| 操作系统 | redhat 6.5 | redhat 6.5 |
| 主机名 | jbdb1 jbdb2 | jbdb1 jbdb2 |
| IP地址 | 192.168.14.145 jbdb1 192.168.14.146 jbdb2 192.168.14.147 jbdb1-vip 192.168.14.148 jbdb2-vip 192.168.14.149 jbdb-scan 192.168.178.131 jbdb1-priv 192.168.178.132 jbdb2-priv | 192.168.14.164 jbdb1 192.168.14.165 jbdb2 192.168.14.166 jbdb1-vip 192.168.14.167 jbdb2-vip 192.168.14.168 jbdb-scan 192.168.179.133 jbdb1-priv 192.168.179.134 jbdb2-priv |
| 数据库版本 | 11.2.0.4 | 11.2.0.4 |
| db_name | jbdb | jbdb |
| db_unique_name | jbdb | jgyldb |
| instance_name | jbdb1 jbdb2 | jgyldb1 jgyldb2 |
| service_name | jbdb | jbdb |
| 软件安装 | 安装GI+数据库软件+创建数据库 | 安装GI+数据库软件 |
| ArchiveFile | +data01/jbdb/archive | +data01/jgyldb/archive |
| DB Storage | ASM | ASM |
| ASM for DB files | +data01/jbdb | +data01/jgyldb |
| ASM for LOG files | +data01/jbdb | +data01/jgyldb |
| ORACLE_HOME | /u01/app/oracle/product/11.2.0/dbhome_1 | /u01/app/oracle/product/11.2.0/dbhome_1 |
#主机名因为是搭建GI环境后进行克隆的，不好修改备库的主机名，所以主备库主机名这里是一致的。
二、ADG配置
1、配置域名解析文件
1、主库所有节点
192.168.14.145 jbdb1
192.168.14.146 jbdb2
192.168.14.147 jbdb1-vip
192.168.14.148 jbdb2-vip
192.168.14.149 jbdb-scan
192.168.178.131 jbdb1-priv
192.168.178.132 jbdb2-priv
192.168.14.164 jgyldb1
192.168.14.165 jgyldb2
192.168.14.166 jgyldb1-vip
192.168.14.167 jgyldb2-vip
192.168.14.168 jgyldb-scan
2、备库所有节点
192.168.14.164 jbdb1
192.168.14.165 jbdb2
192.168.14.166 jbdb1-vip
192.168.14.167 jbdb2-vip
192.168.14.168 jbdb-scan
192.168.179.133 jbdb1-priv
192.168.179.134 jbdb2-priv
192.168.14.145 jgyldb1
192.168.14.146 jgyldb2
192.168.14.147 jgyldb1-vip
192.168.14.148 jgyldb2-vip
192.168.14.149 jgyldb-scan
因主机名一致的问题，在写host文件时都是反过来写的。
2、主库开启归档、开启附加日志
#配置归档路径和开启附加日志
SQL>alter system set log_archive_dest_1='location=+data01/jbdb/archive' scope=spfile;
SQL>alter database force logging;
#开启归档
[grid@jbdb1 ~]$ srvctl stop database -d jbdb
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA01/jbdb/archive
Oldest online log sequence 6
Next log sequence to archive 7
Current log sequence 7
SQL> shutdown immediate
#启动数据库
[grid@jbdb1 ~]$ srvctl start database -d jbdb
#检查归档和附加日志
SQL> select name,open_mode,force_logging from v$database;
NAME OPEN_MODE FOR
--------- -------------------- ---
JBDB READ WRITE YES
SQL>
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 JBDB 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_jbdb1.f'; # default
RMAN>
4、主库配置 Standby Redo Log
#查看 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/jbdb/redo02.log
1 +DATA01/jbdb/redo01.log
3 +DATA01/jbdb/redo03.log
4 +DATA01/jbdb/redo04.log
#添加Standby redo log
SQL> alter database add standby logfile thread 1 group 5 '+DATA01/jbdb/redo05_1.log' size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 '+DATA01/jbdb/redo06_1.log' size 50M;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 '+DATA01/jbdb/redo07_1.log' size 50M;
Database altered.
SQL> alter database add standby logfile thread 2 group 8 '+DATA01/jbdb/redo08_1.log' size 50M;
Database altered.
SQL> alter database add standby logfile thread 2 group 9 '+DATA01/jbdb/redo09_1.log' size 50M;
Database altered.
SQL> alter database add standby logfile thread 2 group 10 '+DATA01/jbdb/redo10_1.log' size 50M;
Database altered.
SQL>
#检查确认
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.
standby redo log的大小与redo log大小相同。组数需要比在线日志多一组。
5、配置 TNSNAMES.ORA 文件
以oracle用户在主库所有节点上配置 tnsname.ora
vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
JBDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jbdb1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jbdb)
)
)
JGYLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jgyldb1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jbdb)
)
)
将主库的tnsnames.ora文件拷贝到备库的所有节点，将修改如下，将名称替换下。
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@jbdb2 admin]$ scp tnsnames.ora jgyldb1:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
tnsnames.ora 100% 505 0.5KB/s 00:00
[oracle@jbdb2 admin]$ scp tnsnames.ora jgyldb2:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
tnsnames.ora 100% 505 0.5KB/s 00:00
[oracle@jbdb2 admin]$
登陆备库查看效果如下：
[oracle@jbdb2 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.
JGYLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jbdb1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jgyldb)
)
)
JBDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jgyldb1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jbdb)
)
)
6、配置主库 ADG 参数
1、备份spfile文件
SQL>create pfile=’/u01/initbak20211013.ora’ from spfile;
2、配置主库参数
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(jbdb,jgyldb)' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+data01/jbdb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jbdb' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=jgyldb SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jgyldb' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both;
SQL> alter system set LOG_FILE_NAME_CONVERT='+DATA01/jgyldb','+DATA01/jbdb' scope=spfile;
SQL> alter system set DB_FILE_NAME_CONVERT='+DATA01/jgyldb','+DATA01/jbdb' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=both;
SQL> alter system set FAL_SERVER='jgyldb' scope=both;
3、重启数据库
srvctl stop database -d jbdb
srvctl start database -d jbdb
这里不能按照单实例轮询重启，必须所有节点都关闭重启。
7、配置密码文件
1、将 主库1 节点的密码文件拷贝到主库的所有节点和备库上：
[oracle@jbdb1 ~]$ cd $ORACLE_HOME/dbs
[oracle@jbdb1 dbs]$ scp orapwjbdb1 jgyldb1:$ORACLE_HOME/dbs/orapwjgyldb1
[oracle@jbdb1 dbs]$ scp orapwjbdb1 jgyldb2:$ORACLE_HOME/dbs/orapwjgyldb2
8、创建备库初始化参数文件
1、在主库上生成最新 pfile 文件 ，并拷贝到备库上， 并重命名为 initjgyldb.ora
SQL> create pfile='/tmp/initjbdb.ora' from spfile;
File created.
SQL> exit
[oracle@jbdb1 ~]$ scp /tmp/initjbdb.ora jgyldb1:/tmp/initjgyldb.ora
2、修改备库上的 pfile 文件
[oracle@jbdb1 ~]$ more /tmp/initjbdb.ora
jgyldb2.__db_cache_size=260046848
jgyldb1.__db_cache_size=289406976
jgyldb2.__java_pool_size=4194304
jgyldb1.__java_pool_size=4194304
jgyldb2.__large_pool_size=8388608
jgyldb1.__large_pool_size=8388608
jgyldb2.__pga_aggregate_target=318767104
jgyldb1.__pga_aggregate_target=276824064
jgyldb2.__sga_target=465567744
jgyldb1.__sga_target=507510784
jgyldb2.__shared_io_pool_size=0
jgyldb1.__shared_io_pool_size=0
jgyldb2.__shared_pool_size=180355072
jgyldb1.__shared_pool_size=192937984
jgyldb2.__streams_pool_size=0
jgyldb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/jgyldb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA01/jgyldb/control01.ctl','+DATA01/jgyldb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA01/jbdb','+DATA01/jgyldb'
*.db_name='jbdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='jbdb'
jgyldb2.instance_number=2
jgyldb1.instance_number=1
*.log_archive_config='DG_CONFIG=(jgyldb,jbdb)'
*.log_archive_dest_1='LOCATION=+data01/jgyldb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jgyldb'
*.log_archive_dest_2='SERVICE=jbdb SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jbdb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA01/jbdb','+DATA01/jgyldb'
*.memory_target=783286272
*.open_cursors=300
*.processes=150
*.remote_listener='jbdb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
jgyldb2.thread=2
jgyldb1.thread=1
jgyldb1.undo_tablespace='UNDOTBS1'
jgyldb2.undo_tablespace='UNDOTBS2'
[oracle@jbdb1 ~]$
3、和主库对比修改了哪些地方，主库配置如下所示：
jbdb2.__db_cache_size=260046848
jbdb1.__db_cache_size=289406976
jbdb2.__java_pool_size=4194304
jbdb1.__java_pool_size=4194304
jbdb2.__large_pool_size=8388608
jbdb1.__large_pool_size=8388608
jbdb2.__pga_aggregate_target=318767104
jbdb1.__pga_aggregate_target=276824064
jbdb2.__sga_target=465567744
jbdb1.__sga_target=507510784
jbdb2.__shared_io_pool_size=0
jbdb1.__shared_io_pool_size=0
jbdb2.__shared_pool_size=180355072
jbdb1.__shared_pool_size=192937984
jbdb2.__streams_pool_size=0
jbdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/jbdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA01/jbdb/control01.ctl','+DATA01/jbdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA01/jgyldb','+DATA01/jbdb'
*.db_name='jbdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jbdbXDB)'
*.fal_server='jgyldb'
db_unique_name=jgyldb
jbdb2.instance_number=2
jbdb1.instance_number=1
*.log_archive_config='DG_CONFIG=(jbdb,jgyldb)'
*.log_archive_dest_1='LOCATION=+data01/jbdb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jbdb'
*.log_archive_dest_2='SERVICE=jgyldb SYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jgyldb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA01/jgyldb','+DATA01/jbdb'
*.memory_target=783286272
*.open_cursors=300
*.processes=150
*.remote_listener='jbdb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
jbdb2.thread=2
jbdb1.thread=1
jbdb1.undo_tablespace='UNDOTBS1'
jbdb2.undo_tablespace='UNDOTBS2'
9、创建备库必要目录
1、查看路径
[oracle@jbdb1 ~]$ cat /tmp/initjbdb.ora | grep /
*.audit_file_dest='/u01/app/oracle/admin/jgyldb/adump'
*.control_files='+DATA01/jgyldb/control01.ctl','+DATA01/jgyldb/control02.ctl'
*.db_file_name_convert='+DATA01/jbdb','+DATA01/jgyldb'
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_dest_1='LOCATION=+data01/jgyldb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jgyldb'
*.log_file_name_convert='+DATA01/jbdb','+DATA01/jgyldb'
[oracle@jbdb1 ~]$
2、在备库主机上创建需要的数据库目录
[oracle@jbdb2 ~]$ mkdir -p /u01/app/oracle/admin/jgyldb/adump
[oracle@jbdb2 ~]$ mkdir -p /u01/app/oracle/admin/jgyldb/dpdump
[oracle@jbdb2 ~]$ mkdir -p /u01/app/oracle/admin/jgyldb/hdump
[oracle@jbdb2 ~]$ mkdir -p /u01/app/oracle/admin/jgyldb/pfile
$ su – grid
$ asmcmd
ASMCMD> +DATA01/jgyldb
ASMCMD> mkdir +DATA01/jgyldb
ASMCMD> mkdir +data01/jgyldb/archive
ASMCMD> exit
10、备库 LISTENER 配置
1、grid用户修改listener.ora文件，备库两个节点都需要修改，这里以节点1为例
[grid@jbdb1 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 = jgyldb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=jgyldb1)
)
)
[grid@jbdb1 admin]$
2、重启监听
[grid@jbdb1 admin]$ srvctl start listener -l LISTENER
11、备库 ADG 初始化
1、启动备库到nomount状态
SQL> startup nomount pfile='/tmp/initjbdb.ora';
2、主库和备库测试连接
[oracle@jbdb2 ~]$ sqlplus sys/admin123@jgyldb as sysdba
[oracle@jbdb2 ~]$ sqlplus sys/admin123@jbdb as sysdba
3、将数据库注册到集群中，1节点执行
[oracle@jbdb1 ~]$ srvctl add database -d jgyldb -o /u01/app/oracle/product/11.2.0/dbhome_1
[oracle@jbdb1 ~]$ srvctl add instance -d jgyldb -i jgyldb1 -n jbdb1
[oracle@jbdb1 ~]$ srvctl add instance -d jgyldb -i jgyldb2 -n jbdb2
4、备库通过 RMAN duplicate 完成进行备库初始化同步
[oracle@jbdb1 ~]$ rman target sys/admin123@jbdb auxiliary sys/admin123@jgyldb
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 15 02:13:43 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JBDB (DBID=680083408)
connected to auxiliary database: JBDB (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
Starting Duplicate Db at 15-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=jgyldb1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwjbdb1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwjgyldb1' ;
}
executing Memory Script
Starting backup at 15-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 instance=jbdb1 device type=DISK
Finished backup at 15-OCT-21
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA01/jgyldb/control01.ctl';
restore clone controlfile to '+DATA01/jgyldb/control02.ctl' from
'+DATA01/jgyldb/control01.ctl';
}
executing Memory Script
Starting backup at 15-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_jbdb1.f tag=TAG20211015T021345 RECID=8 STAMP=1085969628
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 15-OCT-21
Starting restore at 15-OCT-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 15-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/jgyldb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"+DATA01/jgyldb/system01.dbf";
set newname for datafile 2 to
"+DATA01/jgyldb/sysaux01.dbf";
set newname for datafile 3 to
"+DATA01/jgyldb/undotbs01.dbf";
set newname for datafile 4 to
"+DATA01/jgyldb/users01.dbf";
set newname for datafile 5 to
"+DATA01/jgyldb/undotbs02.dbf";
set newname for datafile 6 to
"+DATA01/jgyldb/data01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+DATA01/jgyldb/system01.dbf" datafile
2 auxiliary format
"+DATA01/jgyldb/sysaux01.dbf" datafile
3 auxiliary format
"+DATA01/jgyldb/undotbs01.dbf" datafile
4 auxiliary format
"+DATA01/jgyldb/users01.dbf" datafile
5 auxiliary format
"+DATA01/jgyldb/undotbs02.dbf" datafile
6 auxiliary format
"+DATA01/jgyldb/data01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA01/jgyldb/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
Starting backup at 15-OCT-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA01/jbdb/system01.dbf
output file name=+DATA01/jgyldb/system01.dbf tag=TAG20211015T021406
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA01/jbdb/sysaux01.dbf
output file name=+DATA01/jgyldb/sysaux01.dbf tag=TAG20211015T021406
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/jbdb/data01.dbf
output file name=+DATA01/jgyldb/data01.dbf tag=TAG20211015T021406
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/jbdb/undotbs01.dbf
output file name=+DATA01/jgyldb/undotbs01.dbf tag=TAG20211015T021406
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/jbdb/undotbs02.dbf
output file name=+DATA01/jgyldb/undotbs02.dbf tag=TAG20211015T021406
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA01/jbdb/users01.dbf
output file name=+DATA01/jgyldb/users01.dbf tag=TAG20211015T021406
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-OCT-21
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "+DATA01/jbdb/archive/2_15_1085768275.dbf" auxiliary format
"+DATA01/jgyldb/archive/2_15_1085768275.dbf" archivelog like
"+DATA01/jbdb/archive/1_21_1085768275.dbf" auxiliary format
"+DATA01/jgyldb/archive/1_21_1085768275.dbf" archivelog like
"+DATA01/jbdb/archive/2_16_1085768275.dbf" auxiliary format
"+DATA01/jgyldb/archive/2_16_1085768275.dbf" archivelog like
"+DATA01/jbdb/archive/1_19_1085768275.dbf" auxiliary format
"+DATA01/jgyldb/archive/1_19_1085768275.dbf" archivelog like
"+DATA01/jbdb/archive/1_20_1085768275.dbf" auxiliary format
"+DATA01/jgyldb/archive/1_20_1085768275.dbf" ;
catalog clone archivelog "+DATA01/jgyldb/archive/2_15_1085768275.dbf";
catalog clone archivelog "+DATA01/jgyldb/archive/1_21_1085768275.dbf";
catalog clone archivelog "+DATA01/jgyldb/archive/2_16_1085768275.dbf";
catalog clone archivelog "+DATA01/jgyldb/archive/1_19_1085768275.dbf";
catalog clone archivelog "+DATA01/jgyldb/archive/1_20_1085768275.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 15-OCT-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=15 RECID=31 STAMP=1085969658
output file name=+DATA01/jgyldb/archive/2_15_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=21 RECID=32 STAMP=1085969755
output file name=+DATA01/jgyldb/archive/1_21_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=16 RECID=33 STAMP=1085969756
output file name=+DATA01/jgyldb/archive/2_16_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=19 RECID=29 STAMP=1085966064
output file name=+DATA01/jgyldb/archive/1_19_1085768275.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=20 RECID=30 STAMP=1085969653
output file name=+DATA01/jgyldb/archive/1_20_1085768275.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 15-OCT-21
cataloged archived log
archived log file name=+DATA01/jgyldb/archive/2_15_1085768275.dbf RECID=1 STAMP=1085969775
cataloged archived log
archived log file name=+DATA01/jgyldb/archive/1_21_1085768275.dbf RECID=2 STAMP=1085969775
cataloged archived log
archived log file name=+DATA01/jgyldb/archive/2_16_1085768275.dbf RECID=3 STAMP=1085969775
cataloged archived log
archived log file name=+DATA01/jgyldb/archive/1_19_1085768275.dbf RECID=4 STAMP=1085969775
cataloged archived log
archived log file name=+DATA01/jgyldb/archive/1_20_1085768275.dbf RECID=5 STAMP=1085969776
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=1085969776 file name=+DATA01/jgyldb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=1085969776 file name=+DATA01/jgyldb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1085969776 file name=+DATA01/jgyldb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=1085969776 file name=+DATA01/jgyldb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=1085969776 file name=+DATA01/jgyldb/undotbs02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=1085969776 file name=+DATA01/jgyldb/data01.dbf
contents of Memory Script:
{
set until scn 1403506;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 15-OCT-21
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 20 is already on disk as file +DATA01/jgyldb/archive/1_20_1085768275.dbf
archived log for thread 1 with sequence 21 is already on disk as file +DATA01/jgyldb/archive/1_21_1085768275.dbf
archived log for thread 2 with sequence 15 is already on disk as file +DATA01/jgyldb/archive/2_15_1085768275.dbf
archived log for thread 2 with sequence 16 is already on disk as file +DATA01/jgyldb/archive/2_16_1085768275.dbf
archived log file name=+DATA01/jgyldb/archive/1_20_1085768275.dbf thread=1 sequence=20
archived log file name=+DATA01/jgyldb/archive/2_15_1085768275.dbf thread=2 sequence=15
archived log file name=+DATA01/jgyldb/archive/1_21_1085768275.dbf thread=1 sequence=21
archived log file name=+DATA01/jgyldb/archive/2_16_1085768275.dbf thread=2 sequence=16
media recovery complete, elapsed time: 00:00:04
Finished recover at 15-OCT-21
Finished Duplicate Db at 15-OCT-21
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@jbdb1 ~]$
5、在线复制完成之后，打开备库
#打开数据库
SQL> alter database open;
Database altered.
#查看状态
SQL> select name,db_unique_name,open_mode from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE
--------- ------------------------------ --------------------
JBDB jgyldb 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
--------- ------------------------------ --------------------
JBDB jgyldb READ ONLY WITH APPLY
12、启动另外一个节点
1、使用pfile生成spfile
SQL> create spfile='+DATA01/jgyldb/spfilejgyldb.ora' from pfile='/tmp/initjbdb.ora ;
2、创建pfile，内容如下，并将节点1的pfile 拷贝到节点2：
[oracle@jbdb1 ~]$ echo "SPFILE='+DATA01/jgyldb/spfilejgyldb.ora'" > $ORACLE_HOME/dbs/initjgyldb1.ora
[oracle@jbdb1 ~]$ scp $ORACLE_HOME/dbs/initjgyldb1.ora jbdb2:$ORACLE_HOME/dbs/initjgyldb2.ora
3、重启数据库
[grid@jbdb2 ~]$ srvctl stop database -d jgyldb
[grid@jbdb2 ~]$ srvctl start database -d jgyldb
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
--------- ------------------------------ -------------------- ----------------
JBDB jbdb READ WRITE PRIMARY
#备库
SQL> select name,db_unique_name,open_mode,database_role from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
JBDB jgyldb READ ONLY PHYSICAL STANDBY
13、测试adg同步是否正常
1、主库测试建表空间，建用户，建表、插入数据。
SQL> create tablespace adg datafile '+data01/jbdb/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> /
PID PROCESS STATUS CLIENT_P CLIENT_PID THREAD# SEQ# BLOCK# BLOCKS
-------- --------- ------------ -------- ---------------------------------------- ------- ---------- ---------- ----------
23647 ARCH CLOSING ARCH 23647 1 24 2 944
23649 ARCH CLOSING ARCH 23649 1 22 1 241
23651 ARCH CONNECTED ARCH 23651 0 0 0 0
23653 ARCH CLOSING ARCH 23653 1 24 1 945
23477 LGWR WRITING LGWR 23477 1 25 7674 1
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@jbdb1 ~]$ 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 = jgyldb)
# (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
# (SID_NAME=jgyldb1)
# )
# )
[grid@jbdb1 ~]$
3、主备切换：
注：rac to rac的adg切换只需在其中一个节点执行相应命令即可，执行完后，再把另外的节点open
1、在做切换时，主库需要将除了 1 节点之外的所有节点全部关闭：
[grid@jbdb1 ~]$ srvctl stop instance -d jbdb -i jbdb2
2、在主库上执行如下操作， 将主库切换为备库角色
#查看switchover 状态
SQL> select open_mode, switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
JBDB 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
---------------- ------------
jgyldb1 MOUNTED
[root@jbdb2 ~]# su - grid
[grid@jbdb2 ~]$ srvctl stop database -d jgyldb
[grid@jbdb2 ~]$ srvctl start database -d jgyldb
#查看数据库状态，模式已经变成主库
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
jgyldb1 OPEN
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
JBDB PRIMARY FAILED DESTINATION
5、启动备库（原先的主库）
#启动数据库
[grid@jbdb2 ~]$ srvctl start database -d jbdb
#查看数据库模式，此时主库已经切换为物理备库
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
JBDB 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
--------- ---------------- --------------------
JBDB PRIMARY TO STANDBY
SQL>
#查看备库状态
SQL> select open_mode, switchover_status from v$database;
OPEN_MODE SWITCHOVER_STATUS
-------------------- --------------------
READ ONLY WITH APPLY NOT ALLOWED
SQL>
6、测试同步
在新主库创建表和数据，测试新备库 是否正常同步。
4、发生问题：
1、初始化报错
[oracle@jbdb1 ~]$ rman target sys/admin123@jbdb auxiliary sys/admin123@jgyldb
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 15 02:10:59 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JBDB (DBID=680083408)
connected to auxiliary database: JBDB (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
Starting Duplicate Db at 15-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=125 instance=jgyldb1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwjbdb1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwjgyldb1' ;
}
executing Memory Script
Starting backup at 15-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=jbdb1 device type=DISK
Finished backup at 15-OCT-21
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA01/jgyldb/control01.ctl';
restore clone controlfile to '+DATA01/jgyldb/control02.ctl' from
'+DATA01/jgyldb/control01.ctl';
}
executing Memory Script
Starting backup at 15-OCT-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/15/2021 02:11:15
ORA-19558: error de-allocating device
ORA-19557: device error, device type: DISK, device name:
ORA-17627: ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 18 Serial number: 29
ORA-17627: ORA-01041: internal error. hostdef extension doesn't exist
ORA-17627: ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 143 Serial number: 13
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
[oracle@jbdb1 ~]$
经查是备库的oracle文件权限不对，导致无法访问asm磁盘组，需要手动更改两边节点权限
[root@jbdb2 ~]# chown oracle:asmadmin /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[root@jbdb2 ~]# chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[root@jbdb2 ~]# ls -l /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 239626641 Oct 11 22:57 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
2、tns访问异常，需要将主备库的tnsnames.ora文件地址全部改成单节点的地址
JGYLDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jbdb1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jgyldb)
)
)
JBDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = jgyldb1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jbdb)
)
)
参考
https://www.cnblogs.com/lijiaman/p/11404413.html
类似资料：