8.3.2 Dropping Tablespaces and Deleting Datafiles
8.3.2 刪除表空間和刪除數據文件
When you delete one or more datafiles
or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles to the standby database. The following sections provide examples of dropping
tablespaces and deleting datafiles when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO or MANUAL.
當你在主庫刪除一個或多個數據文件或者刪除一個或多個表空間時，你也需要在備庫刪除相關的數據文件，以下部分提供的例子，當STANDBY_FILE_MANAGEMENT參數設置為AUTO或者MANUAL時關於刪除表空間和刪除數據文件。
8.3.2.1 When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL
8.3.2.1 當STANDBY_FILE_MANAGEMENT設置為AUTO或者MANUAL時
The following procedure works whether the STANDBY_FILE_MANAGEMENT initialization parameter is set to either MANUAL or AUTO, as follows:
以下的步驟不管STANDBY_FILE_MANAGEMENT參數設置為MANUAL還是AUTO，都能工作，如下：
Drop the tablespace from the primary database:
1.從主庫刪除一個表空間
SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
Make sure that Redo Apply is running (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, Redo Apply is running.
確保重做應用在運行，這樣備庫就會應用這些變化。如果以下的查詢返回的MRP或者MRP0進程，則說明重做應用在運行
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
To verify that deleted datafiles are no longer part of the database, query the V$DATAFILE view.
驗證已經刪除的數據文件不再存在數據庫中，可查詢V$DATAFILE視圖。
Delete the corresponding datafile on the standby system after the archived redo log file was applied to the standby database. For example:
在歸檔重做日志被應用再備庫之後，在備庫刪除相應的數據文件，例如：
% rm /disk1/oracle/oradata/payroll/s2tbs_4.dbf
On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:
在主庫中，確保備庫應用刪除表空間的重做信息之後，你可以從表空間移除數據文件，例如：
% rm /disk1/oracle/oradata/payroll/tbs_4.dbf
8.3.2.2 Using DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
8.3.2.2 使用DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the datafiles on both the primary and standby databases. To use this statement, the STANDBY_FILE_MANAGEMENT initialization
parameter must be set to AUTO. For example, to drop the tablespace at the primary site:
你可以在主庫上使用DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES來刪除主庫和備庫的數據文件，前提是STANDBY_FILE_MANAGEMENT必須設置為AUTO,例如，在主庫上刪除一個表空間：
SQL> DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
###########################################################################################################
我的實驗：主庫使用DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES刪除表空間，備庫中的STANDBY_FILE_MANAGEMENT參數設置為AUTO
主庫：PROD
備庫：PRODSTD
1.首先查看STANDBY_FILE_MANAGEMENT參數是否設置為AUTO
SYS@PRODSTD>show parameter STANDBY_FILE_MANAGEMENT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
2.查看兩邊的表空間以及數據文件
主庫：
SYS@PROD>select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM
/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/PROD/Disk1/example01.dbf EXAMPLE
/u01/app/oracle/oradata/PROD/Disk1/users01.dbf USERS
/u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.db SWTICH_TBS
f
6 rows selected.
備庫：
SYS@PRODSTD>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODSTD/Disk1/system01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/undotbs01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/sysaux01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/example01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/o1_mf_swtich_t_9m21f1f0_.
dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf
6 rows selected.
3.在主庫執行刪除表空間及數據文件
SYS@PROD>drop tablespace SWTICH_TBS including contents and datafiles;
Tablespace dropped.
------------------------------------------------------------------------------------------
主庫告警日志：
Sun Mar 30 10:47:37 2014
drop tablespace SWTICH_TBS including contents and datafiles
Sun Mar 30 10:47:42 2014
Deleted file /u01/app/oracle/oradata/PROD/Disk1/swtich_tbs01.dbf
Completed: drop tablespace SWTICH_TBS including contents and datafiles
------------------------------------------------------------------------------------------
4.再次查看兩邊的表空間以及數據文件
主庫：
SYS@PROD>select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/PROD/Disk1/system01.dbf SYSTEM
/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/PROD/Disk1/example01.dbf EXAMPLE
/u01/app/oracle/oradata/PROD/Disk1/users01.dbf USERS
備庫：
SYS@PRODSTD>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PRODSTD/Disk1/system01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/undotbs01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/sysaux01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/example01.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/o1_mf_swtich_t_9m21f1f0_.dbf
/u01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf
6 rows selected.
此時主庫的歸檔重做日志還沒有傳到備庫。
5.手動切換日志，同步主備庫，讓備庫應用重做日志。
SYS@PROD>alter system switch logfile;
System altered.
-------------------------------------------------------------------------------------------
主庫告警日志：
LNS1 started with pid=57, OS id=3000
Sun Mar 30 10:58:08 2014
Thread 1 advanced to log sequence 34
Current log# 2 seq# 34 mem# 0: /u01/app/oracle/oradata/PROD/Disk1/redo02.log
Current log# 2 seq# 34 mem# 1: /u01/app/oracle/oradata/PROD/Disk2/redo02_1.log
Sun Mar 30 10:58:10 2014
LNS: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2
Sun Mar 30 10:58:11 2014
ARC5: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
備庫告警日志：
Sun Mar 30 10:58:11 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 2939
RFS[3]: Identified database type as 'physical standby'
Sun Mar 30 10:58:11 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 2941
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 6: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby06.log'
Sun Mar 30 10:58:12 2014
RFS[3]: Successfully opened standby log 7: '/u01/app/oracle/oradata/PRODSTD/Disk1/standby07.log'
Sun Mar 30 10:58:17 2014
Media Recovery Log /u01/app/oracle/oradata/PRODSTD/Disk2/arch/1_33_842523531.arc
Recovery deleting file #5:'/u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/o1_mf_swtich_t_9mh1rzf6_.dbf' from controlfile.
Deleted Oracle managed file /u01/app/oracle/oradata/PRODSTD/Disk1/PRODSTD/datafile/o1_mf_swtich_t_9mh1rzf6_.dbf
Recovery dropped tablespace 'SWTICH_TBS'
Media Recovery Waiting for thread 1 sequence 34 (in transit)
-----------------------------------------------------------------------------------------------------------
6.查看備庫數據文件
SYS@PRODSTD>select file#,name from v$datafile;
FILE# NAME
---------- -------------------------------------------------------
1 /u01/app/oracle/oradata/PRODSTD/Disk1/system01.dbf
2 /u01/app/oracle/oradata/PRODSTD/Disk1/undotbs01.dbf
3 /u01/app/oracle/oradata/PRODSTD/Disk1/sysaux01.dbf
4 /u01/app/oracle/oradata/PRODSTD/Disk1/example01.dbf
7 /u01/app/oracle/oradata/PRODSTD/Disk1/users01.dbf
此時備庫的SWITCH_TBS表空間已經被刪除。
###########################################################################################################
