STANDBY_FILE_MANAGEMENT设置为MANUAL，增加及删除表空间和数据文件
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set standby_file_management='MANUAL' scope=both;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL>
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> alter system switch logfile;
System altered
SQL>
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/product/10.2.0/db/dbs/UNNAMED00006
6 rows selected.
SQL>
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST
7 rows selected.
SQL>
可以看到，表空间已经自动创建，但是，数据文件却被起了个怪名字，手工修改其与primary
数据库保持一致.
SQL>alter database create datafile
'/u01/app/oracle/product/10.2.0/db/dbs/UNNAMED00006'
as '/u01/app/oracle/oradata/jytest/mytest01.dbf';
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>
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
/u01/app/oracle/oradata/jytest/mytest01.dbf
6 rows selected.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
MYTEST
7 rows selected.
SQL>
数据还在啊。查看alertjytest.log 文件，发现如下
MRP0: Background Media Recovery terminated with error 1274
Mon Dec 3 17:03:34 2012
重启redo 应用再来看看：
SQL> alter database recover managed standby database disconnect from session;
Database altered.
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> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
HYGEIA
6 rows selected.
SQL>
注意，既使你在primary 数据库执行删除时加上了including 子句，在standby 数据库仍然只会将表空间和数据文件从数据字典中删除，你还需要手工删除表空间涉及的数据文件。