ADG迁移到单实例到RAC

1. 主库准备（orasgl）
1.1 检查主库是否是开启归档模式
#检查归档是否开启
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Current log sequence           11

#创建归档路径
SQL> !mkdir /home/oracle/archlog #在sqlplus中执行linux命令前面需要加'!'

#开启归档模式
#一致性停库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

#开启到mount状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
Database mounted.

#打开归档模式
SQL> alter database archivelog;
Database altered.

#打开数据库
SQL> alter database open;
Database altered.

#修改归档路径
SQL> alter system set log_archive_dest_1='location=/home/oracle/archlog';
System altered.

#检查归档是否开启
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/archlog
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
1.2 启用强日志模式
SQL> alter database force logging;
Database altered.
12
1.3 设置DG相关参数
#检查remote_login_passwordfile 参数（EXCLUSIVE）
SQL> show parameter remote_login_passwordfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

#检查standby_file_management 参数（AUTO）
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
#不是AUTO，修改为AUTO
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.

#检查db_unique_name （有唯一名）
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orasgl

#打开DG开关（orasgl为本库唯一名，orarac为RAC库唯一名）
SQL> alter system set log_archive_config='dg_config=(orasgl,orarac)';
System altered.

#配置本地归档
SQL> alter system set log_archive_dest_1='location=/home/oracle/archlog valid_for=(all_logfiles,all_roles) db_unique_name=orasgl';
System altered.
#激活归档路径1
SQL> alter system set log_archive_dest_state_1='enable';
System altered.

#配置远程归档
SQL> alter system set log_archive_dest_2='service=orarac valid_for=(online_logfiles,primary_role) db_unique_name=orarac';
System altered.
#激活归档路径2
SQL> alter system set log_archive_dest_state_2='enable';
System altered.

#配置fal_client、fal_server
SQL> alter system set fal_client='orasgl';
System altered.

SQL> alter system set fal_server='orarac';
System altered.

#配置文件路径转换（前面为远程库的数据文件路径，后面为本地库的数据文件路径）
SQL> alter system set db_file_name_convert='+RACDATA/oradata/orarac/','/u01/app/oracle/oradata/orasgl/' scope=spfile;
System altered.

SQL> alter system set log_file_name_convert='+RACDATA/oradata/orarac/','/u01/app/oracle/oradata/orasgl/' scope=spfile;
System altered.
#重启数据库，检查scope=spfile选项的配置
SQL> show parameter convert;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +RACDATA/oradata/orarac/, /u01/app/or
                                                 acle/oradata/orasgl/
log_file_name_convert                string      +RACDATA/oradata/orarac/, /u01/app/or
                                                 acle/oradata/orasgl/
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061
1.4 将参数文件、密码文件传输给RAC库
#创建pfile文件
SQL> create pfile from spfile;
File created.
#将参数文件传输给rac1节点
[oracle@orclsgl ~]$ cd $ORACLE_HOME/dbs
[oracle@orclsgl dbs]$ scp initorasgl.ora 172.17.10.203:/tmp
#将密码文件传输给rac1、rac2节点
[oracle@orclsgl dbs]$ scp orapworasgl 172.17.10.203:/tmp
[oracle@orclsgl dbs]$ scp orapworasgl 172.17.10.204:/tmp
123456789
2. 备库准备（orarac）
2.1 将rac1、rac2节点的文件拷贝到指定位置
#rac1节点
[oracle@rac1 tmp]$ mv orapworasgl initorasgl.ora $ORACLE_HOME/dbs
#rac2节点
[oracle@rac2 tmp]$ mv orapworasgl $ORACLE_HOME/dbs
#根据SID修改文件名
#rac1节点
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ mv initorasgl.ora initorarac1.ora
[oracle@rac1 dbs]$ mv orapworasgl orapworarac1
#rac2节点
[oracle@rac2 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac2 dbs]$ mv orapworasgl orapworarac2
123456789101112
2.2 将pfile修改为适用于备库的参数文件
#依照如下配置做修改
[oracle@rac1 dbs]$ vim initorarac1.ora
--------------------------------------------
*.db_domain=''
*.db_recovery_file_dest_size=4385144832
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orasglXDB)'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.undo_tablespace='UNDOTBS1'

#以下参数需要注意

#该参数为数据库名，不可修改
*.db_name='orasgl'
#唯一名，修改为RAC库的唯一名
*.db_unique_name='orarac'
#审计文件所在路径
*.audit_file_dest='/u01/app/oracle/admin/orarac/adump'
#自诊断档案库文件所在路径
*.diagnostic_dest='/u01/app/oracle'
#控制文件放于ASM磁盘组对应位置
*.control_files='+RACDATA/oradata/orarac/control01.ctl','+RACFRA/oracle/fast_recovery_area/orarac/control02.ctl'
#快速恢复区对应路径（直接用磁盘组）
*.db_recovery_file_dest='+RACFRA'
#DG配置，不用改
*.log_archive_config='dg_config=(orasgl,orarac)'
#本地归档路径修改为ASM磁盘组路径，唯一名修改为RAC库唯一名
*.log_archive_dest_1='location=+RACFRA/oracle/archlog valid_for=(all_logfiles,all_roles) db_unique_name=orarac'
#服务名和唯一名修改为单实例库对应的名字
*.log_archive_dest_2='service=orasgl valid_for=(online_logfiles,primary_role) db_unique_name=orasgl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
#修改为RAC库唯一名
*.fal_client='orarac'
#修改为单实例库唯一名
*.fal_server='orasgl'
#与单实例库配置正好相反
*.db_file_name_convert='/u01/app/oracle/oradata/orasgl/','+RACDATA/oradata/orarac/'
#与单实例库配置正好相反
*.log_file_name_convert='/u01/app/oracle/oradata/orasgl/','+RACDATA/oradata/orarac/'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
--------------------------------------------
1234567891011121314151617181920212223242526272829303132333435363738394041424344
2.3 根据参数文件中出现的路径，创建对应的目录
#本地目录
#rac1节点
[oracle@rac1 dbs]$ mkdir -p /u01/app/oracle/admin/orarac/adump
#rac2节点
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/orarac/adump
#ASM磁盘组目录（切换至grid用户，在其中一个节点上操作即可）
[grid@rac2 ~]$ asmcmd
ASMCMD> cd +RACDATA
ASMCMD> mkdir oradata
ASMCMD> cd oradata
ASMCMD> mkdir orarac
ASMCMD> cd +RACFRA
ASMCMD> mkdir oracle
ASMCMD> cd oracle
ASMCMD> mkdir fast_recovery_area
ASMCMD> cd fast_recovery_area
ASMCMD> mkdir orarac
ASMCMD> cd +RACFRA/oracle
ASMCMD> mkdir archlog
#通过命令检查配置文件中涉及到的目录是否均已创建
#rac1节点
[oracle@rac1 dbs]$ ll /u01/app/oracle/admin/orarac/adump
#rac2节点
[oracle@rac2 ~]$ ll /u01/app/oracle/admin/orarac/adump
#ASM磁盘组
[grid@rac2 ~]$ asmcmd
ASMCMD> ls -l +RACDATA/oradata/orarac/
ASMCMD> ls -l +RACFRA/oracle/fast_recovery_area/orarac/
ASMCMD> ls -l +RACFRA/oracle/fast_recovery_area
Type Redund Striped Time Sys Name
N orarac/
ASMCMD> ls -l +RACFRA/oracle/archlog
ASMCMD> ls -l +RACDATA/oradata/orarac/
#没提示报错即为正常
12345678910111213141516171819202122232425262728293031323334
2.4 在rac1节点通过pfile启动数据库到nomount状态
[oracle@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 24 17:58:44 2018

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorarac1.ora'
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
SQL>
1234567891011121314151617
3.配置监听
3.1 配置主库监听（orasgl）
配置主库的listener.ora文件
[oracle@orclsgl ~]$ cd $ORACLE_HOME/network/admin
[oracle@orclsgl admin]$ vim listener.ora
-------------------------------------------
LISTENER=
(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.209)(PORT=1521))
)

SID_LIST_LISTENER=
(SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orasgl)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home)
      (SID_NAME=orasgl)
    )
)

SID_LIST_LISTENER=
(SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orasgl)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home)
      (SID_NAME=orasgl)
    )
)
-------------------------------------------
1234567891011121314151617181920212223242526
启动监听
[oracle@orclsgl admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2018 18:35:21

Starting /u01/app/oracle/product/11.2.0/db_home/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orclsgl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.10.209)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.209)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-DEC-2018 18:35:21
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orclsgl/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.10.209)(PORT=1521)))
Services Summary...
Service "orasgl" has 1 instance(s).
Instance "orasgl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
12345678910111213141516171819202122232425262728293031
配置主库的tnsnames.ora文件
[oracle@orclsgl admin]$ vim tnsnames.ora
-------------------------------------------
orasgl=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.209)(PORT=1521))
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=orasgl)
    )
)

orarac=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.203)(PORT=1521))
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=orarac)
    )
)
-------------------------------------------
123456789101112131415161718192021222324
3.2 配置RAC集群监听（orarac）
配置备库的listener.ora文件
注意RAC集群的监听需在grid用户下配置（节点1配置）
#加入静态监听配置
[grid@rac1 ~]$ cd /u01/grid/network/admin/
[grid@rac1 admin]$ vim listener.ora
-------------------------------------
SID_LIST_LISTENER=
(SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=orarac)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) #此处为oracle的ORACLE_HOME
      (SID_NAME=orarac1)
    )
)
-------------------------------------
12345678910111213
重载监听
[grid@rac1 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2018 19:19:45

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[grid@rac1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-DEC-2018 19:19:48

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                24-DEC-2018 13:04:17
Uptime                    0 days 6 hr. 15 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary...
Service "orarac" has 1 instance(s).
Instance "orarac1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

123456789101112131415161718192021222324252627282930313233
配置备库的tnsnames.ora文件
在oracle用户下建
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@rac1 admin]$ vi tnsnames.ora
-------------------------------------
orasgl=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.209)(PORT=1521))
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=orasgl)
    )
)

orarac=
(DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.10.203)(PORT=1521))
    )
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=orarac)
    )
)
-------------------------------------
12345678910111213141516171819202122232425
配置完成后传输给rac2节点一份
[oracle@rac1 admin]$ scp tnsnames.ora rac2:/u01/app/oracle/product/11.2.0/db_1/network/admin/
tnsnames.ora                                                                                                          100% 388     0.4KB/s   00:00
12
3.3 监听连接测试
单实例测试：
[oracle@orclsgl ~]$ sqlplus sys/oracle@orasgl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 24 19:29:44 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orasgl

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@orclsgl ~]$ sqlplus sys/oracle@orarac as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 24 19:30:42 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orarac

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
12345678910111213141516171819202122232425262728293031323334353637
集群测试：
[oracle@rac1 ~]$ sqlplus sys/oracle@orasgl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 24 19:33:00 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orasgl

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rac1 ~]$ sqlplus sys/oracle@orarac as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 24 19:33:16 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orarac

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
12345678910111213141516171819202122232425262728293031323334353637
4. 数据复制
在单实例数据库上通过RMAN连接单实例数据和RAC集群
[oracle@orclsgl ~]$ rman target sys/oracle@orasgl auxiliary sys/oracle@orarac

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 24 19:36:12 2018

connected to target database: ORASGL (DBID=4180087413)
connected to auxiliary database: ORASGL (not mounted)

RMAN> duplicate target database for standby from active database;
...
...
#过程略
#复制脚本自动运行完exit退出rman即可
#此时rac1节点库已经是mount状态
123456789101112131415
可以登录grid用户，查看文件是否复制正确（主要看数据文件和归档文件）
[root@rac2 ~]# su - grid
[grid@rac2 ~]$ asmcmd
ASMCMD> cd +RACDATA/oradata/orarac
ASMCMD> ls
control01.ctl
example01.dbf
redo01.log
redo02.log
redo03.log
sysaux01.dbf
system01.dbf
undotbs01.dbf
users01.dbf
ASMCMD> cd +RACFRA/oracle/archlog
ASMCMD> ls
1_24_995225080.dbf
1_25_995225080.dbf
1_26_995225080.dbf
123456789101112131415161718
检查归档是否可以正常传输
#主库操作（orasgl）
SQL> select group#,sequence#,status from v$log;
    GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
         1         28 INACTIVE
         2         29 CURRENT
         3         27 INACTIVE
#目前的current日志组的seqence#是29号
#切换日志组
SQL> alter system switch logfile;
System altered.
#查看归档是否生成
[oracle@orclsgl ~]$ cd /home/oracle/archlog/
[oracle@orclsgl archlog]$ ls *29*
1_29_995225080.dbf

#备库操作（orarac）
#登录grid用户，查看asm磁盘组归档路径是否有29号文件
[grid@rac2 ~]$ asmcmd
ASMCMD> cd +RACFRA/oracle/archlog
ASMCMD> ls
1_24_995225080.dbf
1_25_995225080.dbf
1_26_995225080.dbf
1_27_995225080.dbf
1_28_995225080.dbf
1_29_995225080.dbf

#验证成功，归档可以正常传输
#如果归档未正常传输，可以用以下SQL排查
#主库运行：select error from v$archive_dest where dest_id=2; (dest_id为远程归档路径对应的ID)
#根据错误提示排查错误
1234567891011121314151617181920212223242526272829303132
5. 添加日志组
5.1 主库（orasgl）添加日志组（为主库切换为备库做准备）
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

#先改为手动模式
SQL> alter system set standby_file_management=manual;
System altered.

SQL> select a.group#,member,thread#,bytes/1024/1024 MB from v$logfile a, v$log b where a.group#=b.group#;
    GROUP# MEMBER                                                THREAD#         MB
---------- -------------------------------------------------- ---------- ----------
         3 /u01/app/oracle/oradata/orasgl/redo03.log                   1         50
         2 /u01/app/oracle/oradata/orasgl/redo02.log                   1         50
         1 /u01/app/oracle/oradata/orasgl/redo01.log                   1         50
3 rows selected.

#根据redolog的大小设置对应的standbylog
SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/orasgl/standby01.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/orasgl/standby02.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/orasgl/standby03.log' size 50m;
Database altered.

#增加rac2节点使用的redo和standby日志
SQL> alter database add logfile thread 2 '/u01/app/oracle/oradata/orasgl/redo04.log' size 50m;
Database altered.

SQL> alter database add logfile thread 2 '/u01/app/oracle/oradata/orasgl/redo05.log' size 50m;
Database altered.

SQL> alter database add logfile thread 2 '/u01/app/oracle/oradata/orasgl/redo06.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/orasgl/standby04.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/orasgl/standby05.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/orasgl/standby06.log' size 50m;
Database altered.

#启用thread2
SQL> alter database enable thread 2;
Database altered.

#改回自动模式
SQL> alter system set standby_file_management=auto;
System altered.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253
5.2 备库（orarac）添加日志组
SQL> show parameter standby_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

SQL> alter system set standby_file_management=manual;
System altered.
#先修改为手动

#查看redolog位置及大小
SQL> select a.group#,member,thread#,bytes/1024/1024 MB from v$logfile a, v$log b where a.group#=b.group#;
    GROUP# MEMBER                                                THREAD#         MB
---------- -------------------------------------------------- ---------- ----------
         1 +RACDATA/oradata/orarac/redo01.log                          1         50
         2 +RACDATA/oradata/orarac/redo02.log                          1         50
         3 +RACDATA/oradata/orarac/redo03.log                          1         50

#创建对应的standbylog
SQL> alter database add standby logfile thread 1 '+RACDATA/oradata/orarac/standby01.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 1 '+RACDATA/oradata/orarac/standby02.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 1 '+RACDATA/oradata/orarac/standby03.log' size 50m;
Database altered.

#为rac2节点创建对应的redolog和standbylog
SQL> alter database add logfile thread 2 '+RACDATA/oradata/orarac/redo04.log' size 50m;
Database altered.

SQL> alter database add logfile thread 2 '+RACDATA/oradata/orarac/redo05.log' size 50m;
Database altered.

SQL> alter database add logfile thread 2 '+RACDATA/oradata/orarac/redo06.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 2 '+RACDATA/oradata/orarac/standby04.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 2 '+RACDATA/oradata/orarac/standby05.log' size 50m;
Database altered.

SQL> alter database add standby logfile thread 2 '+RACDATA/oradata/orarac/standby06.log' size 50m;
Database altered.

#改回为自动
SQL> alter system set standby_file_management=auto;
System altered.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
6.打开数据库，测试数据同步
#在rac1节点操作
#打开数据库
SQL> alter database open;
Database altered.

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

#检查日志同步情况
SQL> select sequence#,applied from v$archived_log order by 1;
SEQUENCE# APPLIED
---------- ---------
         1 YES
        24 YES
        25 YES
        26 YES
        27 YES
        28 YES
        29 YES

#在主库（orasgl）操作更新表，检查同步
SQL> create table scott.test as select * from scott.emp;
Table created.

SQL> alter system switch logfile;
System altered.

#在备库（orarac）检查同步情况
SQL> select count(*) from scott.test;

COUNT(*)
----------
        14
#同步正常
#可以通过以下SQL检查DG备库运行状态
SQL> select * from v$dataguard_stats;
NAME                             VALUE                UNIT                           TIME_COMPUTED                  DATUM_TIME
-------------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------
transport lag                    +00 00:00:00         day(2) to second(0) interval   12/25/2018 10:24:18            12/25/2018 10:24:18
apply lag                        +00 00:00:00         day(2) to second(0) interval   12/25/2018 10:24:18            12/25/2018 10:24:18
apply finish time                +00 00:00:00.000     day(2) to second(3) interval   12/25/2018 10:24:18
estimated startup time           16                   second                         12/25/2018 10:24:18
12345678910111213141516171819202122232425262728293031323334353637383940414243
8. 创建spfile到ASM磁盘组
在rac1节点操作
#通过pfile创建spfile
SQL> create spfile='+RACDATA/oradata/spfileorarac.ora' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorarac1.ora';
File created.

#修改rac1节点的pfile
#备份原pfile
[oracle@rac1 dbs]$ cp initorarac1.ora initorarac1.ora_bak
#修改rac1的pfile（清空文件内容，修改为如下内容）
[oracle@rac1 dbs]$ vim initorarac1.ora
-------------------------------------------
spfile='+RACDATA/oradata/spfileorarac.ora'
-------------------------------------------

#关闭数据库，重启，验证spfile是否正常
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.

#开启实时同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

#将pfile传给rac2节点，并修改成对应的文件名
[oracle@rac1 dbs]$ scp initorarac1.ora rac2:/u01/app/oracle/product/11.2.0/db_1/dbs/initorarac2.ora
initorarac1.ora 100% 43 0.0KB/s 00:00
12345678910111213141516171819202122232425262728293031323334353637
7. 注册到CRS资源管理
在rac1节点操作即可（oracle用户操作）
#注册数据库
[oracle@rac1 ~]$ srvctl add database -d orarac -n orasgl -o /u01/app/oracle/product/11.2.0/db_1 -p +RACDATA/oradata/spfileorarac.ora -r physical_standby -a "RACDATA,RACFRA"
#各选项代表的意义可以通过srvctl add database -h查看帮助

#注册节点
[oracle@rac1 ~]$ srvctl add instance -d orarac -i orarac1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d orarac -i orarac2 -n rac2

#检查资源配置
[oracle@rac1 ~]$ srvctl config database -d orarac
Database unique name: orarac
Database name: orasgl
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +RACDATA/oradata/spfileorarac.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: orarac
Database instances: orarac1,orarac2
Disk Groups: RACDATA,RACFRA
Mount point paths:
Services:
Type: RAC
Database is administrator managed
123456789101112131415161718192021222324252627
8. 将rac2节点开启
#为rac2节点配置自己的UNDOTBS
#主库（orasgl）操作
SQL> select file_name,bytes/1024/1024 from dba_data_files;
FILE_NAME                                          BYTES/1024/1024
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/orasgl/users01.dbf                       5
/u01/app/oracle/oradata/orasgl/undotbs01.dbf                   100
/u01/app/oracle/oradata/orasgl/sysaux01.dbf                    600
/u01/app/oracle/oradata/orasgl/system01.dbf                    750
/u01/app/oracle/oradata/orasgl/example01.dbf               313.125

#在主库添加UNDOTBS2，RAC库同步（加完可以切一下日志）
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orasgl/undotbs02.dbf' size 100m;
Tablespace created.

#备库（orarac）操作
#检查UNDOTBS2是否已经同步过来
SQL> select file_name from dba_data_files

FILE_NAME
--------------------------------------------------------------------------------
+RACDATA/oradata/orarac/users01.dbf
+RACDATA/oradata/orarac/undotbs01.dbf
+RACDATA/oradata/orarac/sysaux01.dbf
+RACDATA/oradata/orarac/system01.dbf
+RACDATA/oradata/orarac/example01.dbf
+RACDATA/oradata/orarac/undotbs02.dbf

#将数据库转换为rac模式
SQL> alter system set cluster_database=true scope=spfile;
System altered.

SQL> alter system set cluster_database_instances=2 scope=spfile;
System altered.

SQL> alter system set instance_number=1 scope=spfile sid='orarac1';
System altered.

SQL> alter system set instance_number=2 scope=spfile sid='orarac2';
System altered.

SQL> alter system set thread=1 scope=spfile sid='orarac1';
System altered.

SQL> alter system set thread=2 scope=spfile sid='orarac2';
System altered.

SQL> alter system set undo_tablespace=undotbs1 scope=spfile sid='orarac1';
System altered.

SQL> alter system set undo_tablespace=undotbs2 scope=spfile sid='orarac2';
System altered.

#重启数据库（rac1节点）
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             603982928 bytes
Database Buffers          226492416 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

#启动数据库（rac2节点）
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             603982928 bytes
Database Buffers          226492416 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.

#测试数据同步
#主库（orasgl）更新表
SQL> insert into scott.test select * from scott.emp;
14 rows created.

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.

#备库（orarac）节点1检测
SQL> select count(*) from scott.test;

COUNT(*)
----------
28

#备库（orarac）节点2检测
SQL> select count(*) from scott.test;

COUNT(*)
----------
28

#至此完成DG部分的所有操作
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108
步骤4：切换主备
注意：切换之前，除了用于做同步的rac1节点保留，其它节点均需关闭
#关闭rac2节点
[oracle@rac1 ~]$ srvctl stop instance -d orarac -i orarac2

#检查主库（orasgl）状态
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY

#将主库（orasgl）切换为备库
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.

#将新备库（orasgl）启动到mount状态
SQL> startup mount;
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             541068368 bytes
Database Buffers          289406976 bytes
Redo Buffers                2371584 bytes
Database mounted.

#将备库（orarac）切换为主库
SQL> alter database commit to switchover to primary;
Database altered.

#将新主库（orarac）打开
SQL> alter database open;
Database altered.

#将新备库（orasgl）打开
SQL> alter database open;
Database altered.

#检查新主备状态
#主库（orarac）状态
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
ORASGL PRIMARY TO STANDBY

#备库（orasgl）状态
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
ORASGL PHYSICAL STANDBY RECOVERY NEEDED

#备库开启日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

#开启rac2节点
[oracle@rac1 ~]$ srvctl start instance -d orarac -i orarac2

#测试归档是否可以正常传输
#查看当前归档文件编号，主库操作（orarac）
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
        46 CURRENT
        44 INACTIVE
        45 INACTIVE
         8 INACTIVE
         9 INACTIVE
        10 CURRENT

#可以看出46号文件是rac1节点的，10号文件是rac2节点的
#两个节点分别执行切换日志命令
SQL> alter system switch logfile;

#在备库（orasgl）检查是否有归档过来
[oracle@orclsgl ~]$ cd /home/oracle/archlog
[oracle@orclsgl archlog]$ ls 2_10*
2_10_995225080.dbf
[oracle@orclsgl archlog]$ ls 1_46*
1_46_995225080.dbf

#测试数据同步
#主库（orarac）rac1节点操作
SQL> insert into scott.test select * from scott.emp;
14 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from scott.test;
COUNT(*)
----------
42

#备库（orasgl）检查
SQL> select count(*) from scott.test;
COUNT(*)
----------
42

#主库（orarac）rac2节点操作
SQL> insert into scott.test select * from scott.emp;
14 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from scott.test;
COUNT(*)
----------
56

#备库（orasgl）检查
SQL> select count(*) from scott.test;
COUNT(*)
----------
56

#切换成功
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116
至此，单实例通过DG的方式迁移至RAC库的实验已完成
望各位同僚在生产中操作之前也最好先实验一下。
---------------------
作者：术士起个门
来源：CSDN
原文：https://blog.csdn.net/weixin_43767002/article/details/85007321
版权声明：本文为博主原创文章，转载请附上博文链接！

liyf0371

ADG迁移到单实例到RAC