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

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

WBOYOriginal: 2016-06-07 15:54:091032browse

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)

看来这样似乎不行……

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.

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：OCM_Session3_1_CreateanRMANCatalogNext article：DG之物理Standby的failover

See more

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

Related articles