data gurad物理備份方式下standby_file_management為manual時修改表空間的操作

eric0435發表於2012-12-04
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 資料庫仍然只會
將表空間和資料檔案從資料字典中刪除，你還需要手工刪除表空間涉及的資料檔案。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-750529/，如需轉載，請註明出處，否則將追究法律責任。

相關文章