物理DG之Primary增删表空间(数据文件)对Standby的影响
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了物理DG之Primary增删表空间(数据文件)对Standby的影响,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含18051字,纯文字阅读大概需要26分钟。
内容图文
![物理DG之Primary增删表空间(数据文件)对Standby的影响](/upload/InfoBanner/zyjiaocheng/564/dead2d9f0a3e4564a51ad6da0ebd5f5d.jpg)
1.物理Standby从SHUTDOWN状态启动到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从SHUTDOWN状态启动到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
之前没有启动REDO应用,现在来取消此应用肯定会出错。
2.从READ ONLY状态到REDO应用状态
ORCLPDG >alter database recover managed standby database disconnect from session;
Database altered.
ORCLPDG >select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
3.取消REDO应用,到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
管理影响Standby的Primary数据库事件
创建表空间或数据文件:
ORCLPRE >conn
sys/safe@orcl_192.168.1.222 as sysdba
Connected.
ORCLPDG >conn sys/safe@orcls_192.168.1.223 as sysdba
Connected.
1.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.
Standby:
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#;
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
6 rows selected.
未启用REDO:
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#;
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
7 rows selected.
2.standby_file_management:manual
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#;
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
PRI_TBS2 /u01/app/oracle/oradata/orcl/pri2.dbf
8 rows selected.
ORCLPRE >alter system switch logfile;
System altered.
应用REDO:
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,REDO应用被迫中止,需要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.
重新应用REDO应用
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#;
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/oradata/orclstd/pri2.dbf
8 rows selected.
删除表空间:
修改Standby的参数auto
ORCLPDG >alter system set standby_file_management=auto;
System altered.
在Primary端删除表空间
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#;
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.
应用REDO:
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#;
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
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
的确已经删除。
修改Standby的参数manual:
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#;
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
6 rows selected.
切换日志:
ORCLPRE >alter system switch logfile;
System altered.
启用REDO应用,在Standby端查看:
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#;
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
6 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
可见,当standby_file_management=manual时,在Primary端删除时加上了including datafiles字句,Standby数据库
只会将表空间和数据文件从数据字典中删除,表空间涉及的物理文件需要手工删除。
重命名表空间:
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#;
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/rmantbs01.dbf
6 rows selected.
ORCLPRE >alter system switch logfile;
System altered.
切换日志,查看Standby:
手工修改:
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)
看来这样似乎不行……
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#;
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/rmantbs01.dbf
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#;
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/rmantbs01.dbf
6 rows selected.
内容总结
以上是互联网集市为您收集整理的物理DG之Primary增删表空间(数据文件)对Standby的影响全部内容,希望文章能够帮你解决物理DG之Primary增删表空间(数据文件)对Standby的影响所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。