当前位置: 开发笔记 > 运维 > 正文

物理DG之Primary增删表空间（数据文件）对Standby的影响

作者：Elaine桑定_148 | 来源：互联网 | 2018-06-05 15:54

1.物理Standby从ＳＨＵＴＤＯＷＮ状态启动到READONLY状态ORCLPDGselectopen_modefromv$database;OPEN_MODE----------READONLY启动之后默认是READONLY状态。ORCLPDGalterdatabaserecovermanagedstandbydatabasecancel;alterdatabasere

1.物理Standby从ＳＨＵＴＤＯＷＮ状态启动到READ ONLY状态 ORCLPDG select open_mode from v$database; OPEN_MODE ---------- READ ONLY 启动之后默认是READ ONLY状态。 ORCLPDG alter database recover managed standby database cancel; alter database re

1.物理Standby从ＳＨＵＴＤＯＷＮ状态启动到READ ONLY状态
ORCLPDG >select open_mode from v$database;

OPEN_MODE
----------
READ ONLY
启动之后默认是READ ONLY状态。

ORCLPDG >alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

之前没有启动ＲＥＤＯ应用，现在来取消此应用肯定会出错。
２.从READ ONLY状态到ＲＥＤＯ应用状态
ORCLPDG >alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG >select open_mode from v$database;

OPEN_MODE
----------
MOUNTED
３.取消ＲＥＤＯ应用，到READ ONLY状态
ORCLPDG >alter database recover managed standby database cancel;

Database altered.

ORCLPDG >select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

ORCLPDG >alter database open;

Database altered.

ORCLPDG >select open_mode from v$database;

OPEN_MODE
----------
READ ONLY
管理影响Ｓｔａｎｄｂｙ的Ｐｒｉｍａｒｙ数据库事件
创建表空间或数据文件：
ORCLPRE >conn sys/safe@orcl_192.168.1.222 as sysdba
Connected.

ORCLPDG >conn sys/safe@orcls_192.168.1.223 as sysdba
Connected.

１.standby_file_management：auto
ORCLPDG >show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
ORCLPRE >create tablespace pri_tbs datafile '/u01/app/oracle/oradata/orcl/pri.dbf' size 10m;

Tablespace created.

ORCLPRE >col tsname for a20
ORCLPRE >col dfname for a50
ORCLPRE >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

TSNAME DFNAME
-------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
RMANTBS /u01/rec_catalog/rmantbs.dbf
PRI_TBS /u01/app/oracle/oradata/orcl/pri.dbf

7 rows selected.

ORCLPRE >alter system switch logfile;

System altered.
Ｓｔａｎｄｂｙ：
ORCLPDG >col dfname for a50
ORCLPDG >col tsname for a20
ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

6 rows selected.
未启用ＲＥＤＯ：
ORCLPDG >alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG >alter database recover managed standby database cancel;

Database altered.

ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

7 rows selected.
２.standby_file_management：ｍａｎｕａｌ
ORCLPDG >alter system set standby_file_management=manual;

System altered.
测试：
ORCLPRE >create tablespace pri_tbs2 datafile '/u01/app/oracle/oradata/orcl/pri2.dbf' size 5m;

Tablespace created.

ORCLPRE >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

8 rows selected.
ORCLPRE >alter system switch logfile;

System altered.

应用ＲＥＤＯ：
ORCLPDG >alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG >alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
查看/u01/app/oracle/admin/orclstd/bdump/alert_orclstd.log ：

Errors in file /u01/app/oracle/admin/orclstd/bdump/orclstd_mrp0_5968.trc:
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/10.2.0/db_1/dbs/UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/10.2.0/db_1/dbs/UNNAMED00008'
无法识别data file 8，就是刚刚添加的PRI_TBS2 /u01/app/oracle/oradata/orcl/pri2.dbf，ＲＥＤＯ应用被迫中止，需要rename to correct file。
之后，查看
ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

TSNAME DFNAME
-------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/orclstd/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orclstd/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/orclstd/sysaux01.dbf
USERS /u01/app/oracle/oradata/orclstd/users01.dbf
EXAMPLE /u01/app/oracle/oradata/orclstd/example01.dbf
RMANTBS /u01/app/oracle/oradata/orclstd/rmantbs.dbf
PRI_TBS /u01/app/oracle/oradata/orclstd/pri.dbf
PRI_TBS2 /u01/app/oracle/10.2.0/db_1/dbs/UNNAMED00008／／路径严重不正确
修改数据文件的名字到正确的路径下面：
ORCLPDG >alter database create datafile '/u01/app/oracle/10.2.0/db_1/dbs/UNNAMED00008' as '/u01/app/oracle/oradata/orclstd/pri2.dbf';

Database altered.
重新应用ＲＥＤＯ应用

ORCLPDG >alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG >alter database recover managed standby database cancel;

Database altered.

ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

8 rows selected.

删除表空间：
修改Ｓｔａｎｄｂｙ的参数ａｕｔｏ

ORCLPDG >alter system set standby_file_management=auto;

System altered.
在Ｐｒｉｍａｒｙ端删除表空间
ORCLPRE >drop tablespace pri_tbs2 including contents and datafiles;

Tablespace dropped.
including contents and datafiles在删除表空间的同时也自动删除对应的物理文件
ORCLPRE >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

7 rows selected.
切换日志：
ORCLPRE >alter system switch logfile;

System altered.
应用ＲＥＤＯ：
ORCLPDG >alter database recover managed standby database disconnect from session;

Database altered.
ORCLPDG >alter database recover managed standby database cancel;

Database altered.
ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

7 rows selected.
查看物理文件：
ORCLPDG >host dir /u01/app/oracle/oradata/orclstd/*

ORCLPDG >host dir /u01/app/oracle/oradata/orclstd/*
/u01/app/oracle/oradata/orclstd/example01.dbf
/u01/app/oracle/oradata/orclstd/orclstd01.ctl
/u01/app/oracle/oradata/orclstd/orclstd02.ctl
/u01/app/oracle/oradata/orclstd/orclstd03.ctl
/u01/app/oracle/oradata/orclstd/pfile_orclstd.ora
/u01/app/oracle/oradata/orclstd/pri.dbf
/u01/app/oracle/oradata/orclstd/redo01.log
/u01/app/oracle/oradata/orclstd/redo02.log
/u01/app/oracle/oradata/orclstd/redo03.log
/u01/app/oracle/oradata/orclstd/rmantbs.dbf
/u01/app/oracle/oradata/orclstd/sysaux01.dbf
/u01/app/oracle/oradata/orclstd/system01.dbf
/u01/app/oracle/oradata/orclstd/temp01.dbf
/u01/app/oracle/oradata/orclstd/undotbs01.dbf
/u01/app/oracle/oradata/orclstd/users01.dbf

的确已经删除。
修改Ｓｔａｎｄｂｙ的参数ｍａｎｕａｌ：
ORCLPDG >alter system set standby_file_management=manual;

System altered.
ORCLPRE >drop tablespace pri_tbs including contents and datafiles;

Tablespace dropped.
ORCLPRE >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

6 rows selected.
切换日志：
ORCLPRE >alter system switch logfile;

System altered.

启用ＲＥＤＯ应用，在Ｓｔａｎｄｂｙ端查看：
ORCLPDG >alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG >alter database recover managed standby database cancel;

Database altered.
ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

6 rows selected.

好像已经删除了，但是看看操作系统下的物理文件：
ORCLPDG >host dir /u01/app/oracle/oradata/orclstd/*

只会将表空间和数据文件从数据字典中删除，表空间涉及的物理文件需要手工删除。
重命名表空间：
ORCLPRE >alter tablespace rmantbs offline;

Tablespace altered.

ORCLPRE >host mv /u01/rec_catalog/rmantbs.dbf /u01/rec_catalog/rmantbs01.dbf

ORCLPRE >alter tablespace rmantbs online;
alter tablespace rmantbs online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/rec_catalog/rmantbs.dbf'

由于在操作系统里面改过名字，无法识别。还要在数据字典里面修改数据文件路径。
ORCLPRE >alter tablespace rmantbs rename datafile
2 '/u01/rec_catalog/rmantbs.dbf'
3 to
4 '/u01/rec_catalog/rmantbs01.dbf';

Tablespace altered.

ORCLPRE >alter tablespace rmantbs online;

Tablespace altered.

ORCLPRE >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

6 rows selected.
ORCLPRE >alter system switch logfile;

System altered.

切换日志，查看Ｓｔａｎｄｂｙ：
手工修改：
ORCLPDG >host mv /u01/app/oracle/oradata/orclstd/rmantbs.dbf rmantbs01.dbf
修改数据字典中的数据文件路径：
ORCLPDG >alter database rename file '/u01/app/oracle/oradata/orclstd/rmantbs.dbf' to '/u01/app/oracle/oradata/orclstd/rmantbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/orclstd/rmantbs.dbf' to '/u01/app/oracle/oradata/orclstd/rmantbs01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file
'/u01/app/oracle/oradata/orclstd/rmantbs01.dbf' not found
ORA-01110: data file 6: '/u01/app/oracle/oradata/orclstd/rmantbs.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

查看日志：
Errors in file /u01/app/oracle/admin/orclstd/bdump/orclstd_mrp0_7911.trc:
ORA-01110: data file 6: '/u01/app/oracle/oradata/orclstd/rmantbs.dbf'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/orclstd/rmantbs.dbf'
Mon Mar 24 01:53:57 2014
MRP0: Background Media Recovery process shutdown (orclstd)

看来这样&＃20284;乎不行……

ORCLPDG >alter database create datafile '/u01/app/oracle/oradata/orclstd/rmantbs.dbf' as '/u01/app/oracle/oradata/orclstd/rmantbs01.dbf';

Database altered.

ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

6 rows selected.

ORCLPDG >host dir /u01/app/oracle/oradata/orclstd/*
/u01/app/oracle/oradata/orclstd/example01.dbf
/u01/app/oracle/oradata/orclstd/orclstd01.ctl
/u01/app/oracle/oradata/orclstd/orclstd02.ctl
/u01/app/oracle/oradata/orclstd/orclstd03.ctl
/u01/app/oracle/oradata/orclstd/pfile_orclstd.ora
/u01/app/oracle/oradata/orclstd/redo01.log
/u01/app/oracle/oradata/orclstd/redo02.log
/u01/app/oracle/oradata/orclstd/redo03.log
/u01/app/oracle/oradata/orclstd/rmantbs01.dbf
/u01/app/oracle/oradata/orclstd/sysaux01.dbf
/u01/app/oracle/oradata/orclstd/system01.dbf
/u01/app/oracle/oradata/orclstd/temp01.dbf
/u01/app/oracle/oradata/orclstd/undotbs01.dbf
/u01/app/oracle/oradata/orclstd/users01.dbf

ORCLPDG >alter database recover managed standby database disconnect from session;

Database altered.

ORCLPDG >alter database recover managed standby database cancel;

Database altered.
ORCLPDG >select ts.name tsname,df.name dfname from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

6 rows selected.

linux

推荐阅读

linux
软件测试行业深度解析：迈向高薪的必经之路

本文深入探讨了软件测试行业的发展现状及未来趋势，旨在帮助有志于在该领域取得高薪的技术人员明确职业方向和发展路径。 ... [详细]

蜡笔小新 2024-11-21 17:32:44
ubuntu
Ubuntu 16.04 下 Qt 5.5 与 i.MX6 交叉编译环境的搭建指南

本文详细介绍了如何在 Ubuntu 16.04 系统上配置 Qt 5.5 的交叉编译环境，特别针对 i.MX6 平台进行了优化设置。内容涵盖从基本的软件安装到高级配置的全过程。 ... [详细]

蜡笔小新 2024-11-21 17:14:39
unix
CentOS下ProFTPD的安装与配置指南

本文详细介绍在CentOS操作系统上安装和配置ProFTPD服务的方法，包括基本配置、安全设置及高级功能的启用。 ... [详细]

蜡笔小新 2024-11-21 09:45:56
server
探索阿里巴巴的开源世界

从理想主义者的内心深处萌发的技术信仰，推动了云原生技术在全球范围内的快速发展。本文将带你深入了解阿里巴巴在开源领域的贡献与成就。 ... [详细]

蜡笔小新 2024-11-21 09:06:54
ssh
设置Shadowsocks公共代理的关键步骤

本文详细介绍了如何正确设置Shadowsocks公共代理，包括调整超时设置、检查系统限制、防止滥用及遵守DMCA法规等关键步骤。 ... [详细]

蜡笔小新 2024-11-20 20:41:33
server
MySQL启动时遇到错误：ERROR! MySQL未运行但存在锁定文件(/var/lock/subsys/mysql)的解决方案

本文详细介绍了在尝试启动MySQL服务时，如果遇到错误提示‘MySQL未运行，但锁定文件(/var/lock/subsys/mysql)存在’应如何处理，包括具体的操作步骤和可能的原因分析。 ... [详细]

蜡笔小新 2024-11-20 20:19:58
linux
深入体验Python的高级交互式Shell - IPython

IPython 是一个增强型的 Python 交互式 Shell，提供了比标准 Python 控制台更为强大的功能，适用于开发和调试过程。它不仅支持直接执行 Linux 命令，还提供了丰富的特性来提高编程效率。 ... [详细]

蜡笔小新 2024-11-20 19:06:56
linux
Linux环境下高效安装源码软件指南

在Linux系统中，许多应用程序以源代码的形式提供，这给安装带来了挑战。本文旨在介绍一种简化源码软件安装流程的方法，帮助用户更加轻松地完成安装。 ... [详细]

蜡笔小新 2024-11-20 19:04:11
touch
Jupyter Notebook多语言环境搭建指南

本文详细介绍了如何在Linux环境下为Jupyter Notebook配置Python、Python3、R及Go四种编程语言的环境，包括必要的软件安装和配置步骤。 ... [详细]

蜡笔小新 2024-11-20 18:37:27
负载均衡
MongoDB 高可用集群搭建指南：分片、读写分离与负载均衡

本文详细介绍了如何搭建一个高可用的MongoDB集群，包括环境准备、用户配置、目录创建、MongoDB安装、配置文件设置、集群组件部署等步骤。特别关注分片、读写分离及负载均衡的实现。 ... [详细]

蜡笔小新 2024-11-20 18:28:16
server
Linux环境下MySQL的安装与部署指南

本文详细介绍了在Linux操作系统上安装和部署MySQL数据库的过程，包括必要的环境准备、安装步骤、配置优化及安全设置等内容。 ... [详细]

蜡笔小新 2024-11-20 18:10:53
linux
掌握专业技能的学习路径

本文探讨了有效学习专业技能的方法，包括编程语言、操作系统、软件组件及前沿技术的探索，旨在为初学者提供一套系统的自学指南。 ... [详细]

蜡笔小新 2024-11-20 17:01:13
unix
Linux命令操作指南：返回文件头与桌面的方法

本文介绍了在Linux环境下如何有效返回命令行状态、上一级目录及快速查找头文件和函数定义的方法。包括处理长时间运行命令、编辑器退出技巧、目录导航以及文件搜索策略。 ... [详细]

蜡笔小新 2024-11-20 16:56:14
linux
Linux中利用cd命令别名快速切换工作目录

通过设置Linux中的cd命令别名，用户能够更加高效地在复杂的目录结构中进行工作目录的切换。本文介绍了几种实用的方法，帮助减少重复输入路径的工作量。 ... [详细]

蜡笔小新 2024-11-20 16:44:00
linux
如何在Python中调用C++代码

本文介绍了一种方法，通过使用Python的ctypes库来调用C++代码。具体实例为实现一个简单的加法器，并详细说明了从编写C++代码到编译及最终在Python中调用的全过程。 ... [详细]

蜡笔小新 2024-11-20 16:35:59

Elaine桑定_148

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章