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>
切换日志
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.
SQL>
可以看到，表空间和数据文件已经自动创建，你是不是奇怪为什么数据文件路径自动变成了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
SQL>
提示：使用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.
SQL>
得出结论，对于初始化参数STANDBY_FILE_MANAGMENT 设置为auto 的话，对于表空间和数
据文件的操作完全无须dba 手工干预，primary 和standby 都能很好的处理
