版本 11g 主库 ORCL  备库 TESTDG
1添加数据文件或表空间
2删除数据文件或表空间
3使用可传输表空间
4重命名数据文件
5添加或删除重做日志
6使用NOLOGGING或unrecoverable子句操作ddl或dml
7更改初始化参数

一 添加数据文件或表空间
1 standby_file_management =AUTO 时
主库上的操作
ORCL>
ORCL>col tsname for a20
ORCL>col dfname for a50
ORCL>select ts.name tsname,df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
TSNAME               DFNAME
-------------------- --------------------------------------------------
SYSTEM               F:\ORCL\ORADATA\ORCL\SYSTEM01.DBF
SYSAUX               F:\ORCL\ORADATA\ORCL\SYSAUX01.DBF 
UNDOTBS1             F:\ORCL\ORADATA\ORCL\UNDOTBS01.DBF
USERS                F:\ORCL\ORADATA\ORCL\USERS01.DBF 
EXAMPLE              F:\ORCL\ORADATA\ORCL\EXAMPLE01.DBF
TESTDG>alter database recover managed standby database disconnect from session;
数据库已更改。
TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;

TSNAME               DFNAME                                                 
-------------------- -------------------------------------
SYSTEM               D:\ORACLE\TESTDGDATA\SYSTEM01.DBF
SYSAUX               D:\ORACLE\TESTDGDATA\SYSAUX01.DBF 
UNDOTBS1             D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
USERS                D:\ORACLE\TESTDGDATA\USERS01.DBF 
EXAMPLE              D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
--创建表空间或数据文件
ORCL>create tablespace dg_tbs datafile 'f:\orcl\oradata\orcl\dg_tbs01.dbf'

size 100 m;
表空间已创建。
ORCL>select ts.name tsname,df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
TSNAME               DFNAME 
-------------------- ---------------------------------------
SYSTEM               F:\ORCL\ORADATA\ORCL\SYSTEM01.DBF 
SYSAUX               F:\ORCL\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1             F:\ORCL\ORADATA\ORCL\UNDOTBS01.DBF
USERS                F:\ORCL\ORADATA\ORCL\USERS01.DBF
EXAMPLE              F:\ORCL\ORADATA\ORCL\EXAMPLE01.DBF
DG_TBS               F:\ORCL\ORADATA\ORCL\DG_TBS01.DBF
已选择6行。
ORCL>alter system switch logfile;
系统已更改。

TESTDG>--applied redo logs
TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
TSNAME               DFNAME                                                 
-------------------- --------------------------------------------------     
SYSTEM               D:\ORACLE\TESTDGDATA\SYSTEM01.DBF                      
SYSAUX               D:\ORACLE\TESTDGDATA\SYSAUX01.DBF
UNDOTBS1             D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
USERS                D:\ORACLE\TESTDGDATA\USERS01.DBF 
EXAMPLE              D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
DG_TBS               D:\ORACLE\TESTDGDATA\DG_TBS01.DBF ---已成功应用redo
已选择6行。

---当standby_file_management=manual时，在主库端添加数据文件
TESTDG>---设置standby_file_management =manual
TESTDG>alter system set standby_file_management=manual;
系统已更改。

ORCL>alter tablespace dg_tbs add  datafile

'f:\orcl\oradata\orcl\dg_tbs02.dbf' size 100m;
表空间已更改。
ORCL>select ts.name tsname,df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
TSNAME               DFNAME 
-------------------- ----------------------------------------
SYSTEM               F:\ORCL\ORADATA\ORCL\SYSTEM01.DBF  
SYSAUX               F:\ORCL\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1             F:\ORCL\ORADATA\ORCL\UNDOTBS01.DBF
USERS                F:\ORCL\ORADATA\ORCL\USERS01.DBF 
EXAMPLE              F:\ORCL\ORADATA\ORCL\EXAMPLE01.DBF
DG_TBS               F:\ORCL\ORADATA\ORCL\DG_TBS01.DBF
DG_TBS               F:\ORCL\ORADATA\ORCL\DG_TBS02.DBF
已选择7行。
ORCL>alter system switch logfile;
系统已更改。
TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
select ts.name tsname , df.name dfname
*
第 1 行出现错误:
ORA-01089: 正在执行立即关闭 - 不允许进行任何操作  ---
进程 ID: 2580
会话 ID: 170 序列号: 11

TESTDG>select process,status,thread#,sequence#,block#,blocks
  2  from v$managed_standby;
ERROR:
ORA-03114: 未连接到 ORALCE

TESTDG>conn system/yang as sysdba
已连接。
TESTDG>select process,status,thread#,sequence#,block#,blocks
  2  from v$managed_standby;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS             
--------- ------------ ---------- ---------- ---------- ---------- 
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         36       2049        981
ARCH      CLOSING               1         37          1        189
ARCH      CLOSING               1         38       2049       1405

TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;

TSNAME               DFNAME
-------------------- ----------------------------------------
SYSTEM               D:\ORACLE\TESTDGDATA\SYSTEM01.DBF 
SYSAUX               D:\ORACLE\TESTDGDATA\SYSAUX01.DBF
UNDOTBS1             D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
USERS                D:\ORACLE\TESTDGDATA\USERS01.DBF 
EXAMPLE              D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
DG_TBS               D:\ORACLE\TESTDGDATA\DG_TBS01.DBF
DG_TBS               F:\ORCL\PRODUCT\11.1.0\DB_1\DATABASE\UNNAMED00007

已选择7行。
TESTDG>select recovery_mode from v$archive_dest_status where dest_id =2;
RECOVERY_MODE                                                               
-----------------------                                                     
IDLE                                                                        
TESTDG>--最后一条的数据文件名称为系统自己命名的。要dba手工重命名到正确的路径
TESTDG>alter database create datafile 'f:\orcl\product\11.1.0\db_1\database\unnamed00007' as 'd:\oracle\testdgdata\dg_tbs02.dbf';
数据库已更改。
TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;

TSNAME               DFNAME                                                   
-------------------- ---------------------------------------------
SYSTEM               D:\ORACLE\TESTDGDATA\SYSTEM01.DBF
SYSAUX               D:\ORACLE\TESTDGDATA\SYSAUX01.DBF
UNDOTBS1             D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
USERS                D:\ORACLE\TESTDGDATA\USERS01.DBF
EXAMPLE              D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
DG_TBS               D:\ORACLE\TESTDGDATA\DG_TBS01.DBF
DG_TBS               D:\ORACLE\TESTDGDATA\DG_TBS02.DBF
已选择7行。
TESTDG>---记得上面的那个错吧，由于自动创建的数据文件路径不对，应用redo数据失败导致整个redo应用都被中止，解决了此问题后要记得启动redo应用。
TESTDG>alter database recover managed standby database disconnect from session;
数据库已更改。
ORCL>-- 删除表空间
ORCL>drop tablespace dg_tbs including contents and datafiles;
表空间已删除。
ORCL>select ts.name tsname,df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;

TSNAME               DFNAME
-------------------- ----------------------------------------
SYSTEM               F:\ORCL\ORADATA\ORCL\SYSTEM01.DBF 
SYSAUX               F:\ORCL\ORADATA\ORCL\SYSAUX01.DBF
UNDOTBS1             F:\ORCL\ORADATA\ORCL\UNDOTBS01.DBF
USERS                F:\ORCL\ORADATA\ORCL\USERS01.DBF 
EXAMPLE              F:\ORCL\ORADATA\ORCL\EXAMPLE01.DBF                     

ORCL>select sequence# from v$log;
 SEQUENCE#                                                                  
---------- 
        37 
        38 
        39 
ORCL>alter system switch logfile;
系统已更改。
TESTDG>--删除表空间
TESTDG>alter system set standby_file_management=auto;
系统已更改。
TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
TSNAME               DFNAME 
-------------------- ---------------------------------------
SYSTEM               D:\ORACLE\TESTDGDATA\SYSTEM01.DBF 
SYSAUX               D:\ORACLE\TESTDGDATA\SYSAUX01.DBF 
UNDOTBS1             D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
USERS                D:\ORACLE\TESTDGDATA\USERS01.DBF 
EXAMPLE              D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF

----当standb_file_management=manual时，删除表空间和数据文件
ORCL>create tablespace dg_tbs datafile 'f:\orcl\oradata\orcl\dg_tbs01.dbf' size 10 m;
表空间已创建。
ORCL>alter system switch logfile;----使备库应用redo。
系统已更改。

TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;

TSNAME               DFNAME                                                    
-------------------- -----------------------------------
SYSTEM               D:\ORACLE\TESTDGDATA\SYSTEM01.DBF
SYSAUX               D:\ORACLE\TESTDGDATA\SYSAUX01.DBF
UNDOTBS1             D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
USERS                D:\ORACLE\TESTDGDATA\USERS01.DBF  
EXAMPLE              D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
DG_TBS               D:\ORACLE\TESTDGDATA\DG_TBS01.DBF 

已选择6行。
ORCL>drop tablespace dg_tbs including contents and datafiles;
表空间已删除。
ORCL>select ts.name tsname,df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
TSNAME               DFNAME 
-------------------- --------------------------------------------
SYSTEM               F:\ORCL\ORADATA\ORCL\SYSTEM01.DBF  
SYSAUX               F:\ORCL\ORADATA\ORCL\SYSAUX01.DBF  
UNDOTBS1             F:\ORCL\ORADATA\ORCL\UNDOTBS01.DBF 
USERS                F:\ORCL\ORADATA\ORCL\USERS01.DBF 
EXAMPLE              F:\ORCL\ORADATA\ORCL\EXAMPLE01.DBF
ORCL>alter system switch logfile;
系统已更改。
ORCL>select sequence# from v$log;
 SEQUENCE#   
----------    
        40    
        41    
        42    
ORCL>alter system switch logfile;
系统已更改。
ORCL>select sequence# from v$log;
 SEQUENCE#                        
----------     
        43     
        41   
        42   
ORCL>alter system switch logfile;
系统已更改。
ORCL>alter system set log_archive_dest_state_2 =defer;
系统已更改。
   文档上说当使在主库使用drop tablespace including contents and datafiles语句来在主库上同时删除数据文件，必须要使用standby_file_management=auto，没有给出如果使用manual会怎么样，在备库上删除表空间时提示错误，因为是read only的，所以不应该在备库上进行产生redo的操作。我做的实验过程如下：
TESTDG>select ts.name tsname , df.name dfname
  2  from v$tablespace ts,v$datafile df
  3  where ts.ts#=df.ts#;
TSNAME               DFNAME
-------------------- -------------------------------------
SYSTEM               D:\ORACLE\TESTDGDATA\SYSTEM01.DBF
SYSAUX               D:\ORACLE\TESTDGDATA\SYSAUX01.DBF  
UNDOTBS1             D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF 
USERS                D:\ORACLE\TESTDGDATA\USERS01.DBF 
EXAMPLE              D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF 
DG_TBS               D:\ORACLE\TESTDGDATA\DG_TBS01.DBF

已选择6行。

TESTDG>select sequence# from v$log;
 SEQUENCE# 
---------- 
        40  
        42  
        41                                                                     
TESTDG>select sequence# from v$log;
 SEQUENCE#                                                                     
----------
        43
        42 
        41 

TESTDG>drop tablespace dg_tbs include contents and datafiles;
drop tablespace dg_tbs include contents and datafiles
                       *
第 1 行出现错误:
ORA-02173: 无效的 DROP TABLESPACE 选项
TESTDG>drop tablespace dg_tbs including contents and datafiles;
drop tablespace dg_tbs including contents and datafiles
*
第 1 行出现错误:
ORA-01109: 数据库未打开

TESTDG>alter database recovery managed standby database cancel;
alter database recovery managed standby database cancel
                        *
第 1 行出现错误:
ORA-02231: ALTER DATABASE 选项缺失或无效

TESTDG>alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
第 1 行出现错误:
ORA-16136: 受管备用恢复未激活

TESTDG>alter system set standby_file_management=auto;
系统已更改。
          
TESTDG>select process,status,thread#,sequence#,block#,blocks
  2  from v$managed_standby;----查看redo 应用情况。

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS             
--------- ------------ ---------- ---------- ---------- ----------             
ARCH      CLOSING               1         42          1        205             
ARCH      CLOSING               1         39          1        758             
ARCH      CLOSING               1         40          1        982             
ARCH      CLOSING               1         41          1        304             
RFS       IDLE                  0          0          0          0             
RFS       IDLE                  1         43       7977          2             
RFS       IDLE                  0          0          0          0             

已选择7行。
---在备库上手工删除时，提示错误！         
TESTDG>alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
第 1 行出现错误:
ORA-16136: 受管备用恢复未激活
TESTDG>alter database recover managed standby database disconnect from session;
数据库已更改。
TESTDG>drop tablespace dg_tbs including contents and datafiles;
drop tablespace dg_tbs including contents and datafiles
*
第 1 行出现错误:
ORA-16000: 打开数据库以进行只读访问

TESTDG>select process ,client_process,sequence#,status from v$managed_standby;
PROCESS   CLIENT_P  SEQUENCE# STATUS  
--------- -------- ---------- ------------
ARCH      ARCH             42 CLOSING  
ARCH      ARCH             43 CLOSING  
ARCH      ARCH             40 CLOSING  
ARCH      ARCH             41 CLOSING 
RFS       UNKNOWN           0 IDLE  
MRP0      N/A              44 WAIT_FOR_LOG 
RFS       LGWR             44 IDLE   
RFS       UNKNOWN           0 IDLE  -----说明现在没有在应用redo并且在等等44号归档redo 
已选择8行。     
TESTDG>alter database recover managed standby database cancel;
数据库已更改。
TESTDG>shutdown  immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
TESTDG>exit
-----第二次打开备库时，重新应用redo
TESTDG>alter database recover managed standby database disconnect from session;
数据库已更改。
TESTDG>select thread#,sequence#,applied from v$archived_log;

   THREAD#  SEQUENCE# APP                                                      
---------- ---------- ---                                                      
         1         33 YES                                                      
         1         31 YES                                                      
         1         32 YES                                                      
         1         34 YES                                                      
         1         35 YES                                                      
         1         36 YES                                                      
         1         37 YES                                                      
         1         38 YES                                                      
         1         39 YES                                                      
         1         40 YES                                                      
         1         41 YES                                                     
         1         42 YES                                                      
         1         43 YES                                                      
         1         44 YES                                                      
         1         45 YES                                                      
         1         46 YES                                                      
         1         47 YES                                                      
         1         48 YES                                                      
         1         49 NO ---再次查询时为YES,应用redo需要时间的                                                       
已选择19行。
TESTDG>show parameter standby

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- --------------------------
standby_archive_dest                 string      %ORACLE_HOME%\RDBMS 
standby_file_management              string      AUTO                          
TESTDG>select name from v$datafile;
NAME       
---------------------------------------------------------------------------
D:\ORACLE\TESTDGDATA\SYSTEM01.DBF     
D:\ORACLE\TESTDGDATA\SYSAUX01.DBF
D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
D:\ORACLE\TESTDGDATA\USERS01.DBF 
D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
----发现在手工没有删除DG_TBS 的情况下，备库已经成功应用了主机的日志，删除了表空间DG_TBS！
TESTDG>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)    
--------------  
            49 
----三思的书中也是说需要手工使用系统命令删除删除在主机上已经被删除的数据文件。不过我的没有这么做！：
---重命名数据文件，主库对数据文件重命名时，此修改并不会自动传播到standby数据库。不管是auto还是manual，都得DBA手工操作。
ORCL>create tablespace test datafile 'f:\orcl\oradata\orcl\test01.dbf' size  200m;
表空间已创建。
ORCL>select name from v$datafile;
NAME                                                                           
--------------------------------------------------
F:\ORCL\ORADATA\ORCL\SYSTEM01.DBF                                              
F:\ORCL\ORADATA\ORCL\SYSAUX01.DBF                                              
F:\ORCL\ORADATA\ORCL\UNDOTBS01.DBF                                             
F:\ORCL\ORADATA\ORCL\USERS01.DBF                                               
F:\ORCL\ORADATA\ORCL\EXAMPLE01.DBF                                             
F:\ORCL\ORADATA\ORCL\TEST01.DBF         
已选择6行。
ORCL>alter system switch logfile;
系统已更改。
---重命名数据文件
ORCL>alter tablespace test offline;
表空间已更改。
ORCL>host rename f:\orcl\oradata\orcl\test01.dbf test02.dbf
ORCL>alter tablespace test rename datafile
  2  'f:\orcl\oradata\orcl\test01.dbf' to
  3  'f:\orcl\oradata\orcl\test02.dbf';
表空间已更改。
ORCL>alter tablespace test online;
表空间已更改
ORCL>alter system switch logfile;
系统已更改。
ORCL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)                                                                 
--------------                                                                 
            51 
     
TESTDG>select name from v$datafile;
NAME                                                                           
-----------------------------------------------------------------------
D:\ORACLE\TESTDGDATA\SYSTEM01.DBF
D:\ORACLE\TESTDGDATA\SYSAUX01.DBF
D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF
D:\ORACLE\TESTDGDATA\USERS01.DBF 
D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
D:\ORACLE\TESTDGDATA\TEST01.DBF--没有改变
已选择6行。
TESTDG>select thread#,sequence#,applied from v$archived_log
  2  where sequence#=51;
   THREAD#  SEQUENCE# APP 
---------- ---------- --- 
         1         51 YES 
---首先停止应用。
TESTDG>alter database recover managed standby database cancel;
数据库已更改。
---手工更改名称
TESTDG>host rename d:\oracle\testdgdata\test01.dbf test02.dbf
TESTDG>alter database rename file
  2  'd:\oracle\testdgdata\test01.dbf' to
  3  'd:\oracle\testdgdata\test02.dbf';
alter database rename file
*
第 1 行出现错误:
ORA-01511: 重命名日志/数据文件时出错
ORA-01275: 自动进行备用文件管理时, 不允许进行 RENAME 操作。
TESTDG>alter system set standby_file_management=manual;
系统已更改。

TESTDG>alter database rename file
  2  'd:\oracle\testdgdata\test01.dbf' to
  3  'd:\oracle\testdgdata\test02.dbf';
数据库已更改。
TESTDG>select name from v$datafile;
NAME                                                                           
-----------------------------------------
D:\ORACLE\TESTDGDATA\SYSTEM01.DBF    
D:\ORACLE\TESTDGDATA\SYSAUX01.DBF  
D:\ORACLE\TESTDGDATA\UNDOTBS01.DBF 
D:\ORACLE\TESTDGDATA\USERS01.DBF  
D:\ORACLE\TESTDGDATA\EXAMPLE01.DBF
D:\ORACLE\TESTDGDATA\TEST02.DBF  
已选择6行。
---修改后重新开始应用redo
TESTDG>alter system set standby_file_management=auto;
系统已更改。
TESTDG>alter database recover managed standby database disconnect from session;
数据库已更改。
ORCL>alter system switch logfile;
系统已更改。