standby_file_management 参数测试

本次实验主要查看standby_file_management=manual时primary 增加/删除 表空间和数据文件后物理standby的状态. 

 

操作系统：red hat linux enterprises 5

 

ORACLE：11.2.0.1.0

 

 

首先查看standby_file_management模式

standby> show parameter standby_file_management;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      AUTO

 

将其设置为手动

standby> alter system set standby_file_management=manual;

 

System altered.

 

一：测试添加表空间和数据文件.

 

primary创建表空间添加数据文件

primary> create tablespace test datafile '/oracle/oradata/test/test1.dbf' size 100m;

 

Tablespace created.

 

 

查看primary数据文件和表空间

primary> select v$tablespace.name tablespace,v$datafile.name datafile from v$datafile,v$tablespace where (v$tablespace.ts#=v$datafile.ts#);

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

SYSTEM

/oracle/oradata/test/system01.dbf

 

SYSAUX

/oracle/oradata/test/sysaux01.dbf

 

UNDOTBS1

/oracle/oradata/test/undotbs01.dbf

 

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

USERS

/oracle/oradata/test/users01.dbf

 

TEST

/oracle/oradata/test/test1.dbf

 

 

primary>

 

切换日志

primary> alter system switch logfile;

 

System altered.

 

 

查看standby数据文件和表空间

standby> select v$tablespace.name tablespace,v$datafile.name datafile from v$datafile,v$tablespace where (v$tablespace.ts#=v$datafile.ts#);

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

SYSTEM

/oracle/oradata/dgtest/dgtest/system01.dbf

 

SYSAUX

/oracle/oradata/dgtest/dgtest/sysaux01.dbf

 

UNDOTBS1

/oracle/oradata/dgtest/dgtest/undotbs01.dbf

 

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

USERS

/oracle/oradata/dgtest/dgtest/users01.dbf

 

TEST

/oracle/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005

 

 

多出了一个怪名字的记录.

 

到该目录下看看

[oracle@localhost dbs]$ pwd

/oracle/oracle/product/11.2.0/dbhome_1/dbs

[oracle@localhost dbs]$ ls -al

total 9988

drwxr-xr-x  2 oracle oinstall     4096 Mar  5 20:57 .

drwxr-xr-x 72 oracle oinstall     4096 Feb 21 13:29 ..

-rw-rw----  1 oracle oinstall     1544 Feb 22 02:42 hc_DBUA0.dat

-rw-rw----  1 oracle oinstall     1544 Feb 27 16:45 hc_dgtest.dat

-rw-rw----  1 oracle oinstall     1544 Feb 22 02:46 hc_test.dat

-rw-r--r--  1 oracle oinstall     1644 Mar  3 03:56 initdgtest.ora

-rw-r--r--  1 oracle oinstall     2851 May 15  2009 init.ora

-rw-r--r--  1 oracle oinstall     1575 Feb 27 12:46 inittest.ora

-rw-r-----  1 oracle oinstall       24 Feb 27 16:45 lkDGTEST

-rw-r-----  1 oracle oinstall       24 Feb 22 02:46 lkTEST

-rw-r-----  1 oracle oinstall     1536 Feb 28 15:03 orapwdgtest

-rw-r-----  1 oracle oinstall     1536 Feb 28 15:02 orapwdgtest-bak

-rw-r-----  1 oracle oinstall     1536 Feb 22 02:46 orapwtest

-rw-r-----  1 oracle oinstall 10076160 Mar  6 00:01 snapcf_dgtest.f

-rw-r-----  1 oracle oinstall     5632 Mar  6 00:54 spfiledgtest.ora

-rw-r-----  1 oracle oinstall     4608 Feb 27 16:09 spfiletest.ora

 

也没有发现该文件.

 

看看警报文件

 

Sun Mar 06 01:05:31 2011

Media Recovery Log /oracle/oradata/dgtest/dgtest/archive/1_35_744648271.dbf

File #5 added to control file as 'UNNAMED00005' because

the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL

The file should be manually created to continue.

Errors with log /oracle/oradata/dgtest/dgtest/archive/1_35_744648271.dbf

MRP0: Background Media Recovery terminated with error 1274

Errors in file /oracle/oradata/dgtest/diagnostic_dest/diag/rdbms/dgtest/dgtest/trace/dgtest_mrp0_23360.trc:

ORA-01274: cannot add datafile '/oracle/oradata/test/test1.dbf' - file could not be created

Recovery interrupted!

Recovered data files to a consistent state at change 1016926

Errors in file /oracle/oradata/dgtest/diagnostic_dest/diag/rdbms/dgtest/dgtest/trace/dgtest_mrp0_23360.trc:

ORA-01274: cannot add datafile '/oracle/oradata/test/test1.dbf' - file could not be created

MRP0: Background Media Recovery process shutdown (dgtest)

 

看样子只能手工了~

standby> alter database create datafile '/oracle/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005'

  2  as '/oracle/oradata/dgtest/dgtest/test1.dbf';

 

Database altered.

 

然后查询

 

standby> select v$tablespace.name tablespace,v$datafile.name datafile from v$datafile,v$tablespace where (v$tablespace.ts#=v$datafile.ts#);

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

SYSTEM

/oracle/oradata/dgtest/dgtest/system01.dbf

 

SYSAUX

/oracle/oradata/dgtest/dgtest/sysaux01.dbf

 

UNDOTBS1

/oracle/oradata/dgtest/dgtest/undotbs01.dbf

 

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

USERS

/oracle/oradata/dgtest/dgtest/users01.dbf

 

TEST

/oracle/oradata/dgtest/dgtest/test1.dbf

 

 

standby>

 

查看数据目录

[oracle@localhost dgtest]$ pwd

/oracle/oradata/dgtest/dgtest

[oracle@localhost dgtest]$ ls

archive  redo01.log  redo03.log    system01.dbf  test1.dbf      users01.dbf

new      redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf

 

正常了.......

 

查看同步状态

 

standby> select sequence#,applied from v$archived_log;

 

 SEQUENCE# APPLIED

---------- ---------

        30 YES

        29 YES

        31 YES

        32 YES

        33 YES

        34 YES

        35 NO

 

7 rows selected.

 

应用redo

 

standby> alter database recover managed standby database disconnect from session;

 

Database altered.

 

再次查看同步状态

 

standby> select sequence#,applied from v$archived_log;

 

 SEQUENCE# APPLIED

---------- ---------

        30 YES

        29 YES

        31 YES

        32 YES

        33 YES

        34 YES

        35 YES

 

7 rows selected.

 

 

二：测试删除表空间和数据文件

 

primary删除表空间和数据文件

 

primary> drop tablespace test including contents and datafiles;

 

Tablespace dropped.

 

查看primary表空间和数据文件

 

primary> select v$tablespace.name tablespace,v$datafile.name datafile from v$datafile,v$tablespace where (v$tablespace.ts#=v$datafile.ts#);

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

SYSTEM

/oracle/oradata/test/system01.dbf

 

SYSAUX

/oracle/oradata/test/sysaux01.dbf

 

UNDOTBS1

/oracle/oradata/test/undotbs01.dbf

 

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

USERS

/oracle/oradata/test/users01.dbf

 

切换日志

 

primary> alter system switch logfile;

 

System altered.

 

 

查看standby表空间和数据文件

 

standby> select v$tablespace.name tablespace,v$datafile.name datafile from v$datafile,v$tablespace where (v$tablespace.ts#=v$datafile.ts#);

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

SYSTEM

/oracle/oradata/dgtest/dgtest/system01.dbf

 

SYSAUX

/oracle/oradata/dgtest/dgtest/sysaux01.dbf

 

UNDOTBS1

/oracle/oradata/dgtest/dgtest/undotbs01.dbf

 

 

TABLESPACE

------------------------------

DATAFILE

--------------------------------------------------------------------------------

USERS

/oracle/oradata/dgtest/dgtest/users01.dbf

 

看似挺正常的.去数据目录看看

 

[oracle@localhost dgtest]$ pwd

/oracle/oradata/dgtest/dgtest

[oracle@localhost dgtest]$ ls

archive  redo01.log  redo03.log    system01.dbf  test1.dbf      users01.dbf

new      redo02.log  sysaux01.dbf  temp01.dbf    undotbs01.dbf

 

还健在呢~~~

 

不多说了...直接rm吧

 

THE END