data gurad物理备份方式下standby_file_management为auto时修改表空间的操作

STANDBY_FILE_MANAGEMENT设置为AUTO

增加及删除表空间和数据文件
我们先来看看初始化参数的设置： —-standby 数据库操作

SQL> show parameter standby_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

A).增加新的表空间–primary 数据库操作

SQL>CREATE TABLESPACE mytest DATAFILE '/u01/app/oracle/oradata/jytest/mytest01.dbf' size 20M
表空间已创建。

检查刚添加的数据文件

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf
/u01/app/oracle/oradata/jytest/mytest01.dbf

6 rows selected

SQL>

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST

7 rows selected

切换日志

SQL> alter system switch logfile;
系统已更改。

B).验证standby 库–standby 数据库操作

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf
/u01/app/oracle/oradata/jytest/mytest01.dbf

6 rows selected.

SQL>




SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST

7 rows selected.

可以看到，表空间和数据文件已经自动创建，你是不是奇怪为什么数据文件路径自动变成了jytest(因为我这里是主备不在同一台机器上且数据库结构目录相同),因为我们设置了db_file_name_convert 嘛。

C).删除表空间–primary 数据库操作

SQL> drop tablespace mytest including contents and datafiles;

Tablespace dropped

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf

SQL> alter system switch logfile;

System altered

提示：使用including 子句删除表空间时，
D).验证standby 数据库–standby 数据库操作

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jytest/system01.dbf
/u01/app/oracle/oradata/jytest/undotbs01.dbf
/u01/app/oracle/oradata/jytest/sysaux01.dbf
/u01/app/oracle/oradata/jytest/users01.dbf
/u01/app/oracle/oradata/jytest/hygeia01.dbf

SQL>

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA

6 rows selected.

得出结论，对于初始化参数STANDBY_FILE_MANAGMENT 设置为auto 的话，对于表空间和数
据文件的操作完全无须dba 手工干预，primary 和standby 都能很好的处理