data gurad物理备份方式下standby_file_management为manual时修改表空间的操作

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-750529/