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.
可以看到，表空间已经自动创建，但是，数据文件却被起了个怪名字，手工修改其与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
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.
数据还在啊。查看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.
注意，既使你在primary 数据库执行删除时加上了including 子句，在standby 数据库仍然只会
将表空间和数据文件从数据字典中删除，你还需要手工删除表空间涉及的数据文件。