RAC-DG1-DG2 切换
来源:互联网 发布:域名删除时间到了 编辑:程序博客网 时间:2024/05/18 02:04
实施一,灾备中心DG2 搭建及切换演练:
1,主库: 创建备份目录 /backyup, 主库开启force logging. /etc/hosts 下 列出主备灾 主机名及IP.
2,RMAN全备份数据库(或者最近的一次备份)
3,备份tnsname.ora , 密码文件, 通过主创建参数文件pfile
4, 灾备库安装db soft软件,安装 grid 软件
5,拷贝 1,2,3 项 到灾备库 DG2.
6, 修改 灾备pfile 参数文件,一下作参考:
--灾备库DG2 pfile -- (文件系统)
[oracle@ogg ~]$ cat ttt_fpile
ogg.__db_cache_size=452984832
ogg.__java_pool_size=16777216
ogg.__large_pool_size=16777216
ogg.__oracle_base='/dba/app/oracle'#ORACLE_BASE set from environment
ogg.__pga_aggregate_target=469762048
ogg.__sga_target=704643072
ogg.__shared_io_pool_size=0
ogg.__shared_pool_size=201326592
ogg.__streams_pool_size=0
*.audit_file_dest='/dba/app/oracle/admin/dominict/adump'
*.audit_trail='db'
*.cluster_database=false --/* 非集群为false */
*.compatible='11.2.0.0.0'
*.control_files='/dba/app/oracle/product/dominics/controlfile/current.260.874863039','/dba/app/oracle/flash_recovery_area/controlfile/current.260.874863039'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='/dba/app/oracle' --/* 可修改 */
*.db_domain=''
*.db_file_name_convert='+DATALOG/dominic/datafile/','/dba/app/oracle/datafile','+DATALOG/dominic/tempfile/','/dba/app/oracle/tempfile/' --/* 成对出现 */
*.db_name='dominic'
*.db_recovery_file_dest='/dba/app/oracle/archivelog' --/* 可修改 */
*.db_recovery_file_dest_size=6388608000
*.db_unique_name='dominict' --/* 唯一 */
*.diagnostic_dest='/dba/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
*.fal_client='TO_DT' --/* 网络服务名 */
*.fal_server='TO_RAC' --/* 网络服务名*/
*.log_archive_config='dg_config=(dominic,dominict)' --/* 添加 */
*.log_archive_dest_1='location=/dba/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=dominict' --/* 添加 */
*.log_archive_dest_2='service=TO_RAC sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominic' --/* 添加 */
*.log_archive_dest_state_1='enable' --/* 添加 */
*.log_archive_dest_state_2='enable' --/* 添加 */
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATALOG/dominic/onlinelog/','/dba/app/oracle/logfile/','+LOGFILE/dominic/onlinelog/','/dba/app/oracle/flash_recovery_area/logfile/' --/* 添加 */
*.memory_target=1158291200
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.standby_file_management='AUTO' --/* 添加 */
dominic1.undo_tablespace='UNDOTBS1' --/* 修改 */
7,对应pfile 文件,创建必要的路径,如果通过ASM, 可以通过asmcmd 去创建:
8,拷贝tnsname.ora 至$ORACLE_HOME/network/admin/ 下,listener.ora 通过netca or netmgr 创建修改:
--这里 为了便于以后切换,避免修改JDBC service_name, 可以在 备库,灾备库的linstener.ora 添加一个动态的 监听服务名和 主库名称一样。
[oracle@ogg admin]$ cat listener.ora
# listener.ora Network Configuration File: /dba/app/oracle/product/11.2.3/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dominic)
(ORACLE_HOME = /dba/app/oracle/product/11.2.3/dbhome_1)
(SID_NAME = ogg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ogg.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /dba/app/oracle
[oracle@ogg admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /dba/app/oracle/product/11.2.3/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TO_RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dominic-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominic)
)
)
TO_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.188)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominics)
(SID = dominic3)
(UR=A)
)
)
TO_DT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.180)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominict)
(SID = ogg)
# (UR=A)
)
)
9,拷贝密码文件 至 $ORACLE_HOME/dbs 下: 修改为orapw+SID
10,通过pfile 文件 创建spfile 文件: SQL > CREATE SPFILE FROM PFILE='/BACKUP/XX_PFILE';
--如果是 ASM : 则需要先检查一下$ORACLE_HOME/bin oracle 的权限 :-rwsr-s--x 1 oracle asmadmin 232399431 Mar 20 16:36 oracle
$GRID_HOME/bin oracle 的权限 : -rwsr-s--x 1 grid oinstall 203974257 Mar 20 13:24 oracle
-- CREAGTE spfile='+ASM/db_unique_name/spfile+db_unique_name.ora' from pfile ='/backup/xx_pfile';
11, 灾备库启动至nomount 状态, 同时通过tnsping 一下 主库之间的 监听状态。
12,su - oracle $rman target sys/password@to_primary auxiliary sys/password@to_standby;
--根据报错调整修改:
13, RMAN > duplicate target database for standby nofilenamecheck; --主备路径相同模式
RMAN > duplicate target database for standby ; --主备路径不同模式
14, 如果已写备份片没有无效,需要通过catalog 注册, 根据13项 告警报错调整。
15,此时,灾备已搭建完成, 为mount 状态:
16 , 主库动态修改 spfile 参数(参考):
--alter system set db_unique_name='dominic' sid='*' scope=both;
alter system set fal_server='TO_DG' sid='*' scope=both;
alter system set fal_client='TO_RAC' sid='*' scope=both;
alter system set log_archive_config='dg_config=(dominic,dominics)' sid='*' scope=both;
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=dominic' sid='*' scope=both;
alter system set log_archive_dest_2='service=TO_DG sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominics' sid='*' scope=both;
alter system set log_archive_dest_state_1='enable' sid='*' scope=both;
alter system set log_archive_dest_state_2='defer' sid='*' scope=both;
alter system set log_file_name_convert='TO_DG','TO_RAC' sid='*' scope=spfile;
alter system set log_file_name_convert='TO_RAC','TO_DG' sid='*' scope=spfile;
alter system set standby_file_management='AUTO' sid='*' scope=both;
--主库的: 最终参数文件为:--
[oracle@dominic1 ~]$ cat pppppfile
dominic1.__db_cache_size=301989888
dominic2.__db_cache_size=301989888
dominic1.__java_pool_size=16777216
dominic2.__java_pool_size=16777216
dominic1.__large_pool_size=16777216
dominic2.__large_pool_size=16777216
dominic1.__pga_aggregate_target=436207616
dominic2.__pga_aggregate_target=436207616
dominic1.__sga_target=822083584
dominic2.__sga_target=822083584
dominic1.__shared_io_pool_size=0
dominic2.__shared_io_pool_size=0
dominic1.__shared_pool_size=469762048
dominic2.__shared_pool_size=452984832
dominic1.__streams_pool_size=0
dominic2.__streams_pool_size=16777216
*.audit_file_dest='/dba/app/oracle/admin/dominic/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATALOG/dominic/controlfile/current.260.874863039','+LOGFILE/dominic/controlfile/current.256.874863039'
*.db_block_size=8192
*.db_create_file_dest='+DATALOG'
*.db_domain=''
*.db_file_name_convert='TO_DG','TO_RAC','/dba/app/oracle/datafile','+DATALOG/dominic/datafile/','/dba/app/oracle/tempfile/','+DATALOG/dominic/tempfile/'
--/* 因灾备是文件系统,如果都是ASM 存储管理,直接('to_dg','to_rac','to_dt','to_rac') --net service_name 不可修改 */
--/* 推荐使用文件路径,成对出现 (db_file_name_convert and log_file_name_convert)*/
*.db_name='dominic'
*.db_recovery_file_dest='+LOGFILE'
*.db_recovery_file_dest_size=8388608000
*.diagnostic_dest='/dba/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
*.fal_client='TO_RAC' --/* 添加修改 */
*.fal_server='TO_DG' --/* 添加修改 */
dominic2.instance_number=2
dominic1.instance_number=1
*.log_archive_config='dg_config=(dominic,dominics,dominict)' --/* 添加修改 */
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=dominic' --/* 添加修改 */
*.log_archive_dest_2='service=TO_DG sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominics' --/* 添加修改 */
*.log_archive_dest_3='service=TO_DT sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominict' --/* 添加修改 */
*.log_archive_dest_state_1='enable' --/* 添加修改 */
*.log_archive_dest_state_2='enable' --/* 添加修改 */
*.log_archive_dest_state_3='enable' --/* 添加修改 */
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='TO_DG','TO_RAC','/dba/app/oracle/flash_recovery_area/logfile/','+LOGFILE/dominic/onlinelog/','/dba/app/oracle/logfile/','+DATALOG/dominic/onlinelog/'
*.memory_target=1258291200
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_listener='dominic-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.standby_file_management='AUTO' --/* 添加修改 */
dominic2.thread=2
dominic1.thread=1
dominic1.undo_tablespace='UNDOTBS1'
dominic2.undo_tablespace='UNDOTBS2'
--备库DG1参数为--
[oracle@dominic3 ~]$ cat ppfile
*.audit_file_dest='/dba/app/oracle/admin/dominics/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATALOG/dominics/controlfile/current.260.874863039'
*.db_block_size=8192
*.db_create_file_dest='+DATALOG'
*.db_domain=''
*.db_name='dominic'
*.db_unique_name='dominics'
*.fal_client='TO_DG'
*.fal_server='TO_RAC'
*.log_archive_config='dg_config=(dominic,dominics)'
*.log_archive_dest_1='location=+FRALOG valid_for=(all_logfiles,all_roles) db_unique_name=dominics'
*.log_archive_dest_2='service=TO_RAC sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominic'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.standby_file_management='AUTO'
#*.log_file_name_covert='+DATALOG/dominic/onlinelog/','/dba/app/oracle/logfile/','+LOGFILE/dominic/onlinelog/','+DATA/dominics/onlinelog/'
*.log_file_name_convert='TO_RAC','TO_DG'
*.db_file_name_convert='TO_RAC','TO_DG'
*.db_recovery_file_dest='+FRALOG'
*.db_recovery_file_dest_size=6388608000
*.diagnostic_dest='/dba/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1158291200
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
#*.remote_listener='dominic-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=335
dominic1.undo_tablespace='UNDOTBS1'
17 : 灾备库 DG2 增加standby logfile :
SQL > alter system set standby_file_management='MANUAL' scope=both;
SQL > alter database add standby logfile group x ('xx','yy') size zzM ;
18 , 灾备库开启同步:
SQL > alter database recover managed standby database disconnect from session ; --/* async read only logfile */
SQL > alter database recover managed standby database using current logfile disconnect from session ; --/* sync read only logfile */
19, 主句可以开启DG2 日志的传输: SQL > ALTER SYSTEM SET log_archive_dest_state_3=ENABLE SID='*' SCOPE=BOTH;
20, 查看状态: 通过 v$database ,v$archived_log, v$managed_standby,v$standby_log
--------至此, RAC+DG2 搭建完成 ------
21, 这里是2从,可以把DG1 日志接收禁用,然后创建一个闪回点:
SQL > alter database recover managed standby database cancel;
SQL > create resotre point dg_p1 grarantee flashback database;
/* ---------------------------------------------------
select open_mode,database_role,db_unique_name,flashback_on from v$database;
srvctl stop instance -d dominic -n dominic2
alter database commit to switchover to physical standby with session shutdown ;
srvctl stop instance -d dominic -n dominic1
srvctl start instance -d dominic -n dominic1 -o nomount
SQL> alter database mount standby database;
---------------------------------------------------------*/ --主库在切换时,可以把其中的2个实例关闭掉
21 switchover (主+DG2)
一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意:
1)确认主库和从库间网络连接通畅;
2)确认没有活动的会话连接在数据库中;
3)PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;
4)确保STANDBY数据库处于ARCHIVELOG模式;
5)如果设置了REDO应用的延迟,那么将这个设置去掉;
6)确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。
主库:
A. 查看switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$--其他情况 --$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
附1:switchover_status出现session active/not allowed
当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时,在官方文档说转换会不成功,但是我测试的时候成功了。
附2: ora- 01153: an incompatible media recovery is active
运行下面代码
Alter database recover managed standby database finish;
或者Alter database recover managed standby database finish force;
Alter database recover managed standby database disconnect from session;
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
B 切换成备库
SQL>Alter database commit to switchover to physical standby with session shutdown;
或者
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
c启动到mount和应用日志状态
SQL>SHUTDOWN IMMEDIATE
SQL>startup nomount;
SQL>alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
D 查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
备库:
a.查看switchover状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
TO PRIMARY
附1:若不是用此语句切换:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown
补充:若出现:ORA-16139: media recovery required
是因为没有执行:alter database recover managed standby database disconnect from session;
b.切换成主库
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL>shutdown immediate;
SQL>startup;
SQL>altern systemnswitch logfile;
c.查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
验证同步:
SQL> select max(sequence#) from v$archived_log;
1,主库: 创建备份目录 /backyup, 主库开启force logging. /etc/hosts 下 列出主备灾 主机名及IP.
2,RMAN全备份数据库(或者最近的一次备份)
3,备份tnsname.ora , 密码文件, 通过主创建参数文件pfile
4, 灾备库安装db soft软件,安装 grid 软件
5,拷贝 1,2,3 项 到灾备库 DG2.
6, 修改 灾备pfile 参数文件,一下作参考:
--灾备库DG2 pfile -- (文件系统)
[oracle@ogg ~]$ cat ttt_fpile
ogg.__db_cache_size=452984832
ogg.__java_pool_size=16777216
ogg.__large_pool_size=16777216
ogg.__oracle_base='/dba/app/oracle'#ORACLE_BASE set from environment
ogg.__pga_aggregate_target=469762048
ogg.__sga_target=704643072
ogg.__shared_io_pool_size=0
ogg.__shared_pool_size=201326592
ogg.__streams_pool_size=0
*.audit_file_dest='/dba/app/oracle/admin/dominict/adump'
*.audit_trail='db'
*.cluster_database=false --/* 非集群为false */
*.compatible='11.2.0.0.0'
*.control_files='/dba/app/oracle/product/dominics/controlfile/current.260.874863039','/dba/app/oracle/flash_recovery_area/controlfile/current.260.874863039'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='/dba/app/oracle' --/* 可修改 */
*.db_domain=''
*.db_file_name_convert='+DATALOG/dominic/datafile/','/dba/app/oracle/datafile','+DATALOG/dominic/tempfile/','/dba/app/oracle/tempfile/' --/* 成对出现 */
*.db_name='dominic'
*.db_recovery_file_dest='/dba/app/oracle/archivelog' --/* 可修改 */
*.db_recovery_file_dest_size=6388608000
*.db_unique_name='dominict' --/* 唯一 */
*.diagnostic_dest='/dba/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
*.fal_client='TO_DT' --/* 网络服务名 */
*.fal_server='TO_RAC' --/* 网络服务名*/
*.log_archive_config='dg_config=(dominic,dominict)' --/* 添加 */
*.log_archive_dest_1='location=/dba/app/oracle/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=dominict' --/* 添加 */
*.log_archive_dest_2='service=TO_RAC sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominic' --/* 添加 */
*.log_archive_dest_state_1='enable' --/* 添加 */
*.log_archive_dest_state_2='enable' --/* 添加 */
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATALOG/dominic/onlinelog/','/dba/app/oracle/logfile/','+LOGFILE/dominic/onlinelog/','/dba/app/oracle/flash_recovery_area/logfile/' --/* 添加 */
*.memory_target=1158291200
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.standby_file_management='AUTO' --/* 添加 */
dominic1.undo_tablespace='UNDOTBS1' --/* 修改 */
7,对应pfile 文件,创建必要的路径,如果通过ASM, 可以通过asmcmd 去创建:
8,拷贝tnsname.ora 至$ORACLE_HOME/network/admin/ 下,listener.ora 通过netca or netmgr 创建修改:
--这里 为了便于以后切换,避免修改JDBC service_name, 可以在 备库,灾备库的linstener.ora 添加一个动态的 监听服务名和 主库名称一样。
[oracle@ogg admin]$ cat listener.ora
# listener.ora Network Configuration File: /dba/app/oracle/product/11.2.3/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dominic)
(ORACLE_HOME = /dba/app/oracle/product/11.2.3/dbhome_1)
(SID_NAME = ogg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ogg.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /dba/app/oracle
[oracle@ogg admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /dba/app/oracle/product/11.2.3/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TO_RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dominic-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominic)
)
)
TO_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.188)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominics)
(SID = dominic3)
(UR=A)
)
)
TO_DT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.180)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dominict)
(SID = ogg)
# (UR=A)
)
)
9,拷贝密码文件 至 $ORACLE_HOME/dbs 下: 修改为orapw+SID
10,通过pfile 文件 创建spfile 文件: SQL > CREATE SPFILE FROM PFILE='/BACKUP/XX_PFILE';
--如果是 ASM : 则需要先检查一下$ORACLE_HOME/bin oracle 的权限 :-rwsr-s--x 1 oracle asmadmin 232399431 Mar 20 16:36 oracle
$GRID_HOME/bin oracle 的权限 : -rwsr-s--x 1 grid oinstall 203974257 Mar 20 13:24 oracle
-- CREAGTE spfile='+ASM/db_unique_name/spfile+db_unique_name.ora' from pfile ='/backup/xx_pfile';
11, 灾备库启动至nomount 状态, 同时通过tnsping 一下 主库之间的 监听状态。
12,su - oracle $rman target sys/password@to_primary auxiliary sys/password@to_standby;
--根据报错调整修改:
13, RMAN > duplicate target database for standby nofilenamecheck; --主备路径相同模式
RMAN > duplicate target database for standby ; --主备路径不同模式
14, 如果已写备份片没有无效,需要通过catalog 注册, 根据13项 告警报错调整。
15,此时,灾备已搭建完成, 为mount 状态:
16 , 主库动态修改 spfile 参数(参考):
--alter system set db_unique_name='dominic' sid='*' scope=both;
alter system set fal_server='TO_DG' sid='*' scope=both;
alter system set fal_client='TO_RAC' sid='*' scope=both;
alter system set log_archive_config='dg_config=(dominic,dominics)' sid='*' scope=both;
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=dominic' sid='*' scope=both;
alter system set log_archive_dest_2='service=TO_DG sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominics' sid='*' scope=both;
alter system set log_archive_dest_state_1='enable' sid='*' scope=both;
alter system set log_archive_dest_state_2='defer' sid='*' scope=both;
alter system set log_file_name_convert='TO_DG','TO_RAC' sid='*' scope=spfile;
alter system set log_file_name_convert='TO_RAC','TO_DG' sid='*' scope=spfile;
alter system set standby_file_management='AUTO' sid='*' scope=both;
--主库的: 最终参数文件为:--
[oracle@dominic1 ~]$ cat pppppfile
dominic1.__db_cache_size=301989888
dominic2.__db_cache_size=301989888
dominic1.__java_pool_size=16777216
dominic2.__java_pool_size=16777216
dominic1.__large_pool_size=16777216
dominic2.__large_pool_size=16777216
dominic1.__pga_aggregate_target=436207616
dominic2.__pga_aggregate_target=436207616
dominic1.__sga_target=822083584
dominic2.__sga_target=822083584
dominic1.__shared_io_pool_size=0
dominic2.__shared_io_pool_size=0
dominic1.__shared_pool_size=469762048
dominic2.__shared_pool_size=452984832
dominic1.__streams_pool_size=0
dominic2.__streams_pool_size=16777216
*.audit_file_dest='/dba/app/oracle/admin/dominic/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATALOG/dominic/controlfile/current.260.874863039','+LOGFILE/dominic/controlfile/current.256.874863039'
*.db_block_size=8192
*.db_create_file_dest='+DATALOG'
*.db_domain=''
*.db_file_name_convert='TO_DG','TO_RAC','/dba/app/oracle/datafile','+DATALOG/dominic/datafile/','/dba/app/oracle/tempfile/','+DATALOG/dominic/tempfile/'
--/* 因灾备是文件系统,如果都是ASM 存储管理,直接('to_dg','to_rac','to_dt','to_rac') --net service_name 不可修改 */
--/* 推荐使用文件路径,成对出现 (db_file_name_convert and log_file_name_convert)*/
*.db_name='dominic'
*.db_recovery_file_dest='+LOGFILE'
*.db_recovery_file_dest_size=8388608000
*.diagnostic_dest='/dba/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
*.fal_client='TO_RAC' --/* 添加修改 */
*.fal_server='TO_DG' --/* 添加修改 */
dominic2.instance_number=2
dominic1.instance_number=1
*.log_archive_config='dg_config=(dominic,dominics,dominict)' --/* 添加修改 */
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=dominic' --/* 添加修改 */
*.log_archive_dest_2='service=TO_DG sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominics' --/* 添加修改 */
*.log_archive_dest_3='service=TO_DT sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominict' --/* 添加修改 */
*.log_archive_dest_state_1='enable' --/* 添加修改 */
*.log_archive_dest_state_2='enable' --/* 添加修改 */
*.log_archive_dest_state_3='enable' --/* 添加修改 */
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='TO_DG','TO_RAC','/dba/app/oracle/flash_recovery_area/logfile/','+LOGFILE/dominic/onlinelog/','/dba/app/oracle/logfile/','+DATALOG/dominic/onlinelog/'
*.memory_target=1258291200
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_listener='dominic-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.standby_file_management='AUTO' --/* 添加修改 */
dominic2.thread=2
dominic1.thread=1
dominic1.undo_tablespace='UNDOTBS1'
dominic2.undo_tablespace='UNDOTBS2'
--备库DG1参数为--
[oracle@dominic3 ~]$ cat ppfile
*.audit_file_dest='/dba/app/oracle/admin/dominics/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATALOG/dominics/controlfile/current.260.874863039'
*.db_block_size=8192
*.db_create_file_dest='+DATALOG'
*.db_domain=''
*.db_name='dominic'
*.db_unique_name='dominics'
*.fal_client='TO_DG'
*.fal_server='TO_RAC'
*.log_archive_config='dg_config=(dominic,dominics)'
*.log_archive_dest_1='location=+FRALOG valid_for=(all_logfiles,all_roles) db_unique_name=dominics'
*.log_archive_dest_2='service=TO_RAC sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=dominic'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.standby_file_management='AUTO'
#*.log_file_name_covert='+DATALOG/dominic/onlinelog/','/dba/app/oracle/logfile/','+LOGFILE/dominic/onlinelog/','+DATA/dominics/onlinelog/'
*.log_file_name_convert='TO_RAC','TO_DG'
*.db_file_name_convert='TO_RAC','TO_DG'
*.db_recovery_file_dest='+FRALOG'
*.db_recovery_file_dest_size=6388608000
*.diagnostic_dest='/dba/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dominicXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1158291200
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
#*.remote_listener='dominic-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=335
dominic1.undo_tablespace='UNDOTBS1'
17 : 灾备库 DG2 增加standby logfile :
SQL > alter system set standby_file_management='MANUAL' scope=both;
SQL > alter database add standby logfile group x ('xx','yy') size zzM ;
18 , 灾备库开启同步:
SQL > alter database recover managed standby database disconnect from session ; --/* async read only logfile */
SQL > alter database recover managed standby database using current logfile disconnect from session ; --/* sync read only logfile */
19, 主句可以开启DG2 日志的传输: SQL > ALTER SYSTEM SET log_archive_dest_state_3=ENABLE SID='*' SCOPE=BOTH;
20, 查看状态: 通过 v$database ,v$archived_log, v$managed_standby,v$standby_log
--------至此, RAC+DG2 搭建完成 ------
21, 这里是2从,可以把DG1 日志接收禁用,然后创建一个闪回点:
SQL > alter database recover managed standby database cancel;
SQL > create resotre point dg_p1 grarantee flashback database;
/* ---------------------------------------------------
select open_mode,database_role,db_unique_name,flashback_on from v$database;
srvctl stop instance -d dominic -n dominic2
alter database commit to switchover to physical standby with session shutdown ;
srvctl stop instance -d dominic -n dominic1
srvctl start instance -d dominic -n dominic1 -o nomount
SQL> alter database mount standby database;
---------------------------------------------------------*/ --主库在切换时,可以把其中的2个实例关闭掉
21 switchover (主+DG2)
一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意:
1)确认主库和从库间网络连接通畅;
2)确认没有活动的会话连接在数据库中;
3)PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;
4)确保STANDBY数据库处于ARCHIVELOG模式;
5)如果设置了REDO应用的延迟,那么将这个设置去掉;
6)确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。
主库:
A. 查看switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$--其他情况 --$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
附1:switchover_status出现session active/not allowed
当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时,在官方文档说转换会不成功,但是我测试的时候成功了。
附2: ora- 01153: an incompatible media recovery is active
运行下面代码
Alter database recover managed standby database finish;
或者Alter database recover managed standby database finish force;
Alter database recover managed standby database disconnect from session;
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
B 切换成备库
SQL>Alter database commit to switchover to physical standby with session shutdown;
或者
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
c启动到mount和应用日志状态
SQL>SHUTDOWN IMMEDIATE
SQL>startup nomount;
SQL>alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
D 查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
备库:
a.查看switchover状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
TO PRIMARY
附1:若不是用此语句切换:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown
补充:若出现:ORA-16139: media recovery required
是因为没有执行:alter database recover managed standby database disconnect from session;
b.切换成主库
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL>shutdown immediate;
SQL>startup;
SQL>altern systemnswitch logfile;
c.查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
验证同步:
SQL> select max(sequence#) from v$archived_log;
0 0
- RAC-DG1-DG2 切换
- 11g Rac 切换
- RAC+DG正常切换
- rac 与 rac one node 的切换
- RAC dataguard切换演练实录
- rac归档切换测试记录
- oracle11g rac服务切换测试
- RAC中的EM切换节点
- DG1——概念简介
- Oracle RAC 归档 与 非归档 切换
- Oracle RAC 归档 与 非归档 切换
- 10G RAC 切换到归档模式
- RAC配置DG,单向不切换
- oracle 10g rac归档模式切换
- RAC+DG切换遇到的问题
- Oracle 10g RAC裸设备管理方式切换
- 为什么RAC比双机热备切换快很多?
- ORACLE 11G RAC下的归档切换
- 欢迎使用CSDN-markdown编辑器
- ThreadLocal-分析-总结
- Hadoop 2.2.0和HBase 0.98.11伪分布式
- Ueditor上传配置
- MySQL_GUI_Tools可视化工具的使用!!
- RAC-DG1-DG2 切换
- 非负矩阵分解NMF
- 博客的第一篇___
- android studio的详细使用
- 《淘宝技术这十年》读书笔记 (一).淘宝网技术简介及来源
- Windows查看端口占用及杀掉进程
- DATAGUARD之Redo传输服务
- java中路径问题
- NYOJ120 校园网络(强联通)