Oracle Data Guard_ 主庫刪除資料表空間

8.3.2Dropping Tablespaces and Deleting Datafiles8.3.2 刪除資料表空間和刪除資料檔案 

When you delete one or moredatafiles 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 theSTANDBY_FILE_MANAGEMENTinitialization parameter is set toAUTOorMANUAL.

當你在主庫刪除一個或多個資料檔案或者刪除一個或多個資料表空間時，你也需要在備庫刪除相關的資料檔案，以下部分提供的例子，當STANDBY_FILE_MANAGEMENT參數設定為AUTO或者MANUAL時關於刪除資料表空間和刪除資料檔案。 

8.3.2.1When STANDBY_FILE_MANAGEMENT Is Set to AUTO or MANUAL8.3.2.1 當STANDBY_FILE_MANAGEMENT設定為AUTO或者MANUAL時 

The following procedure works whether theSTANDBY_FILE_MANAGEMENTinitialization parameter is set to eitherMANUALorAUTO, as follows:

以下的步驟不管STANDBY_FILE_MANAGEMENT參數設定為MANUAL還是AUTO，都能工作，如下：

 

1. Drop the tablespace from the primary database:

   1.從主庫刪除一個資料表空間

   SQL> DROP TABLESPACE tbs_4;SQL> ALTER SYSTEM SWITCH LOGFILE; 

2. 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 theV$DATAFILEview.

   驗證已經刪除的資料檔案不再存在資料庫中，可查詢V$DATAFILE視圖。

    

3. 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 

4. 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.2Using DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES8.3.2.2 使用DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES 

You can issue the SQLDROP TABLESPACE INCLUDING CONTENTS AND DATAFILESstatement on the primary database to delete the datafiles on both the primary and standby databases. To use this statement, theSTANDBY_FILE_MANAGEMENTinitialization parameter must be set toAUTO. 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