oracle 11g rac to rac adg 搭建

最新推荐文章于 2023-08-21 10:27:35 发布

bjywxc

最新推荐文章于 2023-08-21 10:27:35 发布

阅读量2.3k

点赞数 4

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/bjywxc/article/details/120786371

版权

oracle 专栏收录该内容

15 篇文章 5 订阅

订阅专栏

一、环境说明

开始配置 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	jbdb1 jbdb2
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 
jbdb2.__db_cache_size=243269632
jbdb1.__db_cache_size=251658240
jbdb1.__java_pool_size=4194304
jbdb2.__java_pool_size=4194304
jbdb1.__large_pool_size=8388608
jbdb2.__large_pool_size=8388608
jbdb1.__pga_aggregate_target=281018368
jbdb2.__pga_aggregate_target=297795584
jbdb1.__sga_target=503316480
jbdb2.__sga_target=486539264
jbdb1.__shared_io_pool_size=0
jbdb2.__shared_io_pool_size=0
jbdb2.__shared_pool_size=218103808
jbdb1.__shared_pool_size=226492416
jbdb1.__streams_pool_size=0
jbdb2.__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/jgyldb/control01.ctl','+DATA01/jgyldb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA01/jbdb','+DATA01/jgyldb'
*.db_name='jbdb'
*.service_names='jbdb'
*.db_unique_name='jgyldb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jbdbXDB)'
*.fal_server='jbdb'
jbdb2.instance_number=2
jbdb1.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'
jbdb2.thread=2
jbdb1.thread=1
jbdb1.undo_tablespace='UNDOTBS1'
jbdb2.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 = jbdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME=jbdb1)
    )
  )
[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 Sun Oct 17 22:39:23 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 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=jbdb1 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/orapwjbdb1'   ;
}
executing Memory Script

Starting backup at 17-OCT-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=jbdb1 device type=DISK
Finished backup at 17-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 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_jbdb1.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/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 17-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=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/jbdb/sysaux01.dbf
output file name=+DATA01/jgyldb/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/jbdb/data01.dbf
output file name=+DATA01/jgyldb/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/jbdb/undotbs01.dbf
output file name=+DATA01/jgyldb/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/jbdb/undotbs02.dbf
output file name=+DATA01/jgyldb/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/jbdb/users01.dbf
output file name=+DATA01/jgyldb/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/jbdb/archive/2_22_1085768275.dbf" auxiliary format 
 "+DATA01/jgyldb/archive/2_22_1085768275.dbf"   archivelog like 
 "+DATA01/jbdb/archive/2_23_1085768275.dbf" auxiliary format 
 "+DATA01/jgyldb/archive/2_23_1085768275.dbf"   archivelog like 
 "+DATA01/jbdb/archive/1_26_1085768275.dbf" auxiliary format 
 "+DATA01/jgyldb/archive/1_26_1085768275.dbf"   archivelog like 
 "+DATA01/jbdb/archive/1_27_1085768275.dbf" auxiliary format 
 "+DATA01/jgyldb/archive/1_27_1085768275.dbf"   archivelog like 
 "+DATA01/jbdb/archive/2_21_1085768275.dbf" auxiliary format 
 "+DATA01/jgyldb/archive/2_21_1085768275.dbf"   ;
   catalog clone archivelog  "+DATA01/jgyldb/archive/2_22_1085768275.dbf";
   catalog clone archivelog  "+DATA01/jgyldb/archive/2_23_1085768275.dbf";
   catalog clone archivelog  "+DATA01/jgyldb/archive/1_26_1085768275.dbf";
   catalog clone archivelog  "+DATA01/jgyldb/archive/1_27_1085768275.dbf";
   catalog clone archivelog  "+DATA01/jgyldb/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/jgyldb/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/jgyldb/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/jgyldb/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/jgyldb/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/jgyldb/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/jgyldb/archive/2_22_1085768275.dbf RECID=1 STAMP=1086216148

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

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

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

cataloged archived log
archived log file name=+DATA01/jgyldb/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/jgyldb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1086216149 file name=+DATA01/jgyldb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1086216149 file name=+DATA01/jgyldb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1086216149 file name=+DATA01/jgyldb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1086216149 file name=+DATA01/jgyldb/undotbs02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=1086216149 file name=+DATA01/jgyldb/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/jgyldb/archive/1_26_1085768275.dbf
archived log for thread 1 with sequence 27 is already on disk as file +DATA01/jgyldb/archive/1_27_1085768275.dbf
archived log for thread 2 with sequence 22 is already on disk as file +DATA01/jgyldb/archive/2_22_1085768275.dbf
archived log for thread 2 with sequence 23 is already on disk as file +DATA01/jgyldb/archive/2_23_1085768275.dbf
archived log file name=+DATA01/jgyldb/archive/1_26_1085768275.dbf thread=1 sequence=26
archived log file name=+DATA01/jgyldb/archive/2_22_1085768275.dbf thread=2 sequence=22
archived log file name=+DATA01/jgyldb/archive/1_27_1085768275.dbf thread=1 sequence=27
archived log file name=+DATA01/jgyldb/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@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 = jbdb)
#      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
#      (SID_NAME=jbdb1)
#    )
#  )
[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
---------------- ------------
jbdb1          MOUNTED

[root@jbdb2 ~]# su - grid
[grid@jbdb2 ~]$ srvctl stop database -d jbdb
[grid@jbdb2 ~]$ srvctl start database -d jbdb

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

INSTANCE_NAME    STATUS
---------------- ------------
jbdb1          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

如果需要在主库添加或者删除数据文件时，这些文件也会在备库添加或删除，使用如下： sql>alter system set standby_file_management=‘AUTO‘; 默认此参数是manual手工方式

bjywxc

关注

4
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
oracle 11g rac to rac adg 搭建

oracle 11g rac to rac adg 搭建实施一、环境说明1、基础环境二、ADG配置1、配置域名解析文件2、主库开启归档、开启附加日志3、主库配置归档删除策略4、主库配置 Standby Redo Log5、配置 TNSNAMES.ORA 文件6、配置主库 ADG 参数7、配置密码文件8、创建备库初始化参数文件9、创建备库必要目录10、备库 LISTENER 配置11、备库 ADG 初始化12、启动另外一个节点13、测试adg同步是否正常14、清除恢复备库的静态注册文件3、主备切换：4、发生问
复制链接

扫一扫