How To: Snapshot Standby 转换操作步骤
2022/6/30 6:19:32
本文主要是介绍How To: Snapshot Standby 转换操作步骤,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
目录- 简介
- Snapshot Standby Database的特性:
- 一、转换物理备库为快照备库
- 1.设置闪回区
- 2.关闭日志应用
- 3.重启数据库至MOUNT
- 4.转换physical standby为snapshot standby
- 5.打开数据库
- 二、转换快照备库为物理备库
- 1.打开数据库至mount状态
- 2.转换snapshot standby为physical standby
- 3.restart数据库,开启日志应用
简介
Snapshot standby database是ORACLE 11g的新特性。允许Physical standby短时间的使用read write模式。
Snapshot standby是由Physical standby 全新转换而来,可以独立于primary 处理事务,同时能够不断地从primary接受redo data,归档redo data以备后用维护保护。
Snapshot Standby Database的特性:
- Snapshot standby接收并归档redo data,但不应用redo data。
- Snapshot standby转换回physical standby后,开始应用之前接收并归档的redo data。
- 主库传输过来的redo data一直被归档存放起来。
- 转换回physical standby后,所有的本地更新操作将会被丢弃。
- 如果primary移动到新的DB分支(如flashback database,open resetlogs),snapshot standby database会从新的DB分支继续接受redo data。
- Snapshot standby不能作为switchover或者failover的目标库。Snapshot standby必须转换回Physical standby才能执行角色转换。
- DG配置中的一个Standby发生switchover或者failover角色转换为primary后,Snapshot standby可以接受角色转换后的新的primary database 的redo data。
- Snapshot standby不能为最大保护模式的DG配置中唯一的standby。
- 一旦snapshot standby被激活的时间超出了primary 的最大负载时间,再次的本地更新操作将会产生额外的异常。
- Snapshot standby需要设置Fast Recovery Area。
一、转换物理备库为快照备库
1.设置闪回区
alter system set db_recovery_file_dest_size=5G scope=both sid='*'; alter system set db_recovery_file_dest='/oradata/fra' scope=both sid='*';
若为启用FRA时,会遇到错误ORA-38784和ORA-38786。
SQL> alter database convert to snapshot standby; alter database convert to snapshot standby * ERROR at line 1: ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_12/01/2020 10:23:25'. ORA-38786: Recovery area is not enabled.
需要注意的是,启动FRA时,db_recovery_file_dest_size要先于db_recovery_file_dest设置,否则会遇到错误ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
alert.log
Tue Dec 01 10:26:12 2020 ALTER SYSTEM SET db_recovery_file_dest_size='5G' SCOPE=BOTH SID='*'; Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST ALTER SYSTEM SET db_recovery_file_dest='/oradata/fra' SCOPE=BOTH SID='*'; Tue Dec 01 10:26:18 2020 db_recovery_file_dest_size of 5120 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.
2.关闭日志应用
SQL> select name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; NAME DATABASE_ROLE SWITCHOVER_STATUS --------- ---------------- -------------------- SYK PHYSICAL STANDBY NOT ALLOWED SQL> alter database recover managed standby database cancel; Database altered.
3.重启数据库至MOUNT
SQL> shutdown immediate SQL> startup mount
4.转换physical standby为snapshot standby
SQL> alter database convert to snapshot standby; Database altered.
alert.log
Tue Dec 01 10:26:28 2020 alter database convert to snapshot standby Starting background process RVWR Tue Dec 01 10:26:28 2020 RVWR started with pid=24, OS id=34478 Allocated 8388608 bytes in shared pool for flashback generation buffer Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/01/2020 10:26:28 <----- 1 Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 1143899 Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Resetting resetlogs activation ID 1250286088 (0x4a85da08) Online log /oradata/SYK/redo01.log: Thread 1 Group 1 was previously cleared Online log /oradata/SYK/redo02.log: Thread 1 Group 2 was previously cleared Online log /oradata/SYK/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1143897 <----- 2 Tue Dec 01 10:26:28 2020 Setting recovery target incarnation to 3 WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is not set to the value "AUTO". This may cause recovery of the standby database to terminate prior to applying all available redo data. It may be necessary to use the ALTER DATABASE CREATE DATAFILE command to add datafiles created on the primary database. CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby <----- 3 Completed: alter database convert to snapshot standby
- 创建Guaranteed Restore Point;
- 主SCN:1143897
- 完成转换
5.打开数据库
SQL> alter database open; Database altered. SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- READ WRITE SYK SNAPSHOT STANDBY NOT ALLOWED
alert.log
Tue Dec 01 10:26:44 2020 alter database open Tue Dec 01 10:26:44 2020 Assigning activation ID 1250413997 (0x4a87cdad) Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /oradata/SYK/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Dec 01 10:26:44 2020 SMON: enabling cache recovery [34375] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:1273929404 end:1273929484 diff:80 (0 seconds) Dictionary check beginning Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Tue Dec 01 10:26:45 2020 QMNC started with pid=25, OS id=34494 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open
二、转换快照备库为物理备库
1.打开数据库至mount状态
SQL> shutdown immediate SQL> startup mount SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- MOUNTED SYK SNAPSHOT STANDBY NOT ALLOWED
2.转换snapshot standby为physical standby
SQL> alter database convert to physical standby;
此时数据库切换成功后,其状态未非mount状态,需要重启重新mount。
SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database * ERROR at line 1: ORA-01507: database not mounted
alert.log
Tue Dec 01 13:21:27 2020 alter database convert to physical standby ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SYK) Flashback Restore Start Flashback Restore Complete Drop guaranteed restore point <----- 删除GRP Stopping background process RVWR Deleted Oracle managed file /oradata/fra/SYK/flashback/o1_mf_hwcbgnl0_.flb Deleted Oracle managed file /oradata/fra/SYK/flashback/o1_mf_hwcbgp0n_.flb Guaranteed restore point dropped Clearing standby activation ID 1250413997 (0x4a87cdad) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Shutting down archive processes Archiving is disabled Tue Dec 01 13:21:29 2020 ARCH shutting down ARC0: Archival stopped Tue Dec 01 13:21:29 2020 ARCH shutting down ARC3: Archival stopped Tue Dec 01 13:21:29 2020 ARCH shutting down ARC2: Archival stopped Tue Dec 01 13:21:29 2020 ARCH shutting down ARC1: Archival stopped Completed: alter database convert to physical standby <----- 完成切换
3.restart数据库,开启日志应用
SQL> shutdown immediate SQL> startup mount SQL> alter database open read only; SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- READ ONLY SYK PHYSICAL STANDBY RECOVERY NEEDED SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. SQL> select open_mode,name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; OPEN_MODE NAME DATABASE_ROLE SWITCHOVER_STATUS -------------------- --------- ---------------- -------------------- READ ONLY WITH APPLY SYK PHYSICAL STANDBY NOT ALLOWED
alert.log
Tue Dec 01 13:26:41 2020 alter database recover managed standby database using current logfile disconnect from session Attempt to start background Managed Standby Recovery process (SYK) Tue Dec 01 13:26:41 2020 MRP0 started with pid=24, OS id=45436 MRP0: Background Managed Standby Recovery process started (SYK) started logmerger process Tue Dec 01 13:26:46 2020 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 2 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Clearing online redo logfile 1 /oradata/SYK/redo01.log Clearing online log 1 of thread 1 sequence number 1 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /oradata/SYK/redo02.log Clearing online log 2 of thread 1 sequence number 2 Clearing online redo logfile 2 complete Media Recovery Log /oraarch/1_22_1057859074.arc Completed: alter database recover managed standby database using current logfile disconnect from session Media Recovery Log /oradata/fra/SYK/archivelog/2020_12_01/o1_mf_1_23_hwcbph9p_.arc Media Recovery Log /oraarch/1_24_1057859074.arc Recovery of Online Redo Log: Thread 1 Group 13 Seq 25 Reading mem 0 Mem# 0: /oradata/stby_redo13.log
这篇关于How To: Snapshot Standby 转换操作步骤的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-13怎么通过 JavaScript 或其他编程语言来实现监听屏幕高度变化功能?-icode9专业技术文章分享
- 2024-11-12聊聊我们那些年用过的表达式引擎组件
- 2024-11-12让项目数据更有说服力:五款必备数据可视化管理工具推荐
- 2024-11-12人到一定年纪,要学会远离多巴胺
- 2024-11-12解读:精益生产管理的目的是什么?如何操作?
- 2024-11-12Sku预研作业
- 2024-11-12文心一言API密钥:分步申请指南
- 2024-11-12初学者指南:轻松掌握后台交互
- 2024-11-12从零开始学习:封装基础知识详解
- 2024-11-12JSON对象入门教程:轻松掌握基础用法