ITPub博客

首页 > 数据库 > Oracle > Oracle 10g 物理DataGuard扩展表空间

Oracle 10g 物理DataGuard扩展表空间

Oracle 作者:lpwebnet 时间:2014-02-08 11:12:48 0 删除 编辑

我的<>,欢迎观看: http://edu.51cto.com/lecturer/index/user_id-1850277.html

Oracle 10g 物理DataGuard扩展表空间

关于物理DG的扩展表空间,首先要看备库端的参数standby_file_management,该参数可以设置为手动管理(MANUAL)和自动管理(AUTO).通常该参数配置为AUTO,在自动管理模式下,当表空间大小在主库端变更时(不论是alter tablespace add datafile还是alter database datafile *** resize),备库能够自动侦测并调整大小,不需要手动干预。以下是物理dataguard扩展表空间的例子:

oracle 10.2.0.1,linux上的物理DG,首先standby处于日志接受模式:

SQL> select instance_name ,status from v$instance;

INSTANCE_NAME                    STATUS
 -------------------------------- ------------------------
 orcl                             MOUNTED

SQL>



在主库端查看归档日志序号:

SQL>  select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
 --------------
           5593

SQL>



在备库端查看日志应用序号:

select sequence#,applied from v$archived_log;

      5583 YES
       5584 YES

SEQUENCE# APPLIE
 ---------- ------
       5585 YES
       5586 YES
       5587 YES
       5588 YES
       5589 YES
       5590 YES
       5591 YES
       5592 YES
       5593 YES

2528 rows selected.

SQL>



日志应用正常。



在主库端查看表空间使用率如下:

SQL> select t.tablespace_name,t.total "总大小(M)",t.total-f.free "已使用(M)",f.free "剩余容量(M)",
 (t.total-f.free)/t.total*100 "使用率%" from
 (select tablespace_name,sum(bytes)/1024/1024 total
   2    3    4  from dba_data_files group by tablespace_name) t,
   5  (select tablespace_name,sum(bytes)/1024/1024  free
   6  from dba_free_space group by tablespace_name) f
   7  where t.tablespace_name=f.tablespace_name(+)
   8  ;

TABLESPACE_NAME      总大尣?M) 已使産?M) 剩余容量(M) 使用玣?
-------------------- ------------ ------------ --------------- ----------
APPS_DATA                    7524     794.6875       6729.3125 10.5620348
 HALFENTERSEND                1024      59.1875        964.8125  5.7800293
 UNDOTBS1                    10240      78.5625      10161.4375 .767211914
 SYSAUX                        570      528.375          41.625 92.6973684
 BSKILL                       1024     183.0625        840.9375 17.8771973
 HALFENTER                    3118    2044.5625       1073.4375 65.5728833
 USERS                       872.5         64.5             808 7.39255014
DEVELOPE                    11754       2056.5          9697.5 17.4961715
 STATE_DATA                   3072    2157.8125        914.1875 70.2412923
 SYSTEM                        960     582.9375        377.0625 60.7226563
 SKILLS_DATA                 13568   12982.4375        585.5625 95.6842386

TABLESPACE_NAME      总大尣?M) 已使産?M) 剩余容量(M) 使用玣?
-------------------- ------------ ------------ --------------- ----------
MAILCHECK_WORK                256      48.0625        207.9375 18.7744141
 IMPORTUSER                   1024     799.1875        224.8125 78.0456543
 PUBCODE_DATA                  512      253.875         258.125 49.5849609
EMAIL_WORK                  11744    9579.0625       2164.9375 81.5655867
 EMPLY                        1024     863.1875        160.8125 84.2956543
 EMPLYSEND                    1024      11.1875       1012.8125  1.0925293

17 rows selected.

SQL>



这里SKILLS_DATA表空间13568M,使用率达到了95.6842386%。



在备库端查询:

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL>



SQL> select t.tablespace_name,t.total "总大小(M)",t.total-f.free "已使用(M)",f.free "剩余容量(M)",
   2  (t.total-f.free)/t.total*100 "使用率%" from
   3  (select tablespace_name,sum(bytes)/1024/1024 total
   4  from dba_data_files group by tablespace_name) t,
   5  (select tablespace_name,sum(bytes)/1024/1024  free
   6  from dba_free_space group by tablespace_name) f
   7  where t.tablespace_name=f.tablespace_name(+)
   8  ;

TABLESPACE_NAME      总大尣?M) 已使産?M) 剩余容量(M) 使用玣?
-------------------- ------------ ------------ --------------- ----------
APPS_DATA                    7524     794.6875       6729.3125 10.5620348
 HALFENTERSEND                1024      59.1875        964.8125  5.7800293
 UNDOTBS1                    10240      78.5625      10161.4375 .767211914
 SYSAUX                        570      528.375          41.625 92.6973684
 BSKILL                       1024     183.0625        840.9375 17.8771973
HALFENTER                    3118    2044.5625       1073.4375 65.5728833
 USERS                       872.5         64.5             808 7.39255014
 DEVELOPE                    11754       2056.5          9697.5 17.4961715
 STATE_DATA                   3072    2157.8125        914.1875 70.2412923
 SYSTEM                        960     582.9375        377.0625 60.7226563
 SKILLS_DATA                 13568   12982.4375        585.5625 95.6842386

TABLESPACE_NAME      总大尣?M) 已使産?M) 剩余容量(M) 使用玣?
-------------------- ------------ ------------ --------------- ----------
MAILCHECK_WORK                256      48.0625        207.9375 18.7744141
IMPORTUSER                   1024     799.1875        224.8125 78.0456543
 PUBCODE_DATA                  512      253.875         258.125 49.5849609
 EMAIL_WORK                  11744    9579.0625       2164.9375 81.5655867
 EMPLY                        1024     863.1875        160.8125 84.2956543
 EMPLYSEND                    1024      11.1875       1012.8125  1.0925293

17 rows selected.

SQL>



也是完全一致的。



我的环境里,SKILLS_DATA表空间有两个数据文件,但是在主库端和备库端的目录不同:

primary:

SQL>  select file_name from dba_data_files where tablespace_name='SKILLS_DATA';

FILE_NAME
 --------------------------------------------------------------------------------
 /u01/oracle/oradata/research/skills_data.dbf
 /u01/oracle/oradata/research/skills_data01.dbf



standby:

SQL>  select file_name from dba_data_files where tablespace_name='SKILLS_DATA';

FILE_NAME
 --------------------------------------------------------------------------------
 /opt/oracle/oradata/research/skills_data.dbf
 /opt/oracle/oradata/research/skills_data01.dbf



将备库切换回日志接收模式:

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

Database altered.

SQL>



在primary:


 SQL> alter tablespace SKILLS_DATA add datafile '/u01/oracle/oradata/research/skills_data02' size 8G;



primary:

SQL> select file_name from dba_data_files where tablespace_name='SKILLS_DATA';

FILE_NAME
 --------------------------------------------------------------------------------
 /u01/oracle/oradata/research/skills_data.dbf
 /u01/oracle/oradata/research/skills_data01.dbf
 /u01/oracle/oradata/research/skills_data02

SQL> select t.tablespace_name,t.total "总大小(M)",t.total-f.free "已使用(M)",f.free "剩余容量(M)",
   2  (t.total-f.free)/t.total*100 "使用率%" from
   3  (select tablespace_name,sum(bytes)/1024/1024 total
   4  from dba_data_files group by tablespace_name) t,
   5  (select tablespace_name,sum(bytes)/1024/1024  free
   6  from dba_free_space group by tablespace_name) f
   7  where t.tablespace_name=f.tablespace_name(+)
   8  ;

TABLESPACE_NAME      总大尣?M) 已使産?M) 剩余容量(M) 使用玣?
-------------------- ------------ ------------ --------------- ----------
APPS_DATA                    7524     794.6875       6729.3125 10.5620348
 HALFENTERSEND                1024      59.1875        964.8125  5.7800293
 UNDOTBS1                    10240      79.5625      10160.4375 .776977539
 SYSAUX                        570      528.375          41.625 92.6973684
 BSKILL                       1024     183.0625        840.9375 17.8771973
 HALFENTER                    3118    2044.5625       1073.4375 65.5728833
 USERS                       872.5         64.5             808 7.39255014
 DEVELOPE                    11754       2056.5          9697.5 17.4961715
 STATE_DATA                   3072    2157.8125        914.1875 70.2412923
 SYSTEM                        960     582.9375        377.0625 60.7226563
 SKILLS_DATA                 21760      12982.5          8777.5 59.6622243

TABLESPACE_NAME      总大尣?M) 已使産?M) 剩余容量(M) 使用玣?
-------------------- ------------ ------------ --------------- ----------
MAILCHECK_WORK                256      48.0625        207.9375 18.7744141
 IMPORTUSER                   1024     799.1875        224.8125 78.0456543
 PUBCODE_DATA                  512      253.875         258.125 49.5849609
 EMAIL_WORK                  11744    9579.0625       2164.9375 81.5655867
 EMPLY                        1024     863.1875        160.8125 84.2956543
 EMPLYSEND                    1024      11.1875       1012.8125  1.0925293

17 rows selected.

SQL>



standby:

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter datbase open read only;
 alter datbase open read only
       *
 ERROR at line 1:
 ORA-00940: invalid ALTER command


 SQL> alter database open read only;

Database altered.

SQL>



standby:

SQL> select file_name from dba_data_files where tablespace_name='SKILLS_DATA';

FILE_NAME
 --------------------------------------------------------------------------------
 /opt/oracle/oradata/research/skills_data.dbf
 /opt/oracle/oradata/research/skills_data01.dbf

SQL>



standby 切回日志接收模式:




primary:

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>



primary 查询:


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
 --------------
           5598

SQL>



standby端切换到open read only状态,然后再次查询:

standby查询:

     5588 YES
       5589 YES

SEQUENCE# APPLIE
 ---------- ------
       5590 YES
       5591 YES
       5592 YES
       5593 YES
       5594 YES
       5595 YES
       5596 YES
       5597 YES
       5598 YES

2528 rows selected.

SQL>



然后在standby端查询:

SQL> select file_name from dba_data_files where tablespace_name='SKILLS_DATA';

FILE_NAME
 --------------------------------------------------------------------------------
 /opt/oracle/oradata/research/skills_data.dbf
 /opt/oracle/oradata/research/skills_data01.dbf
 /u01/oracle/oradata/research/skills_data02

SQL>



我们可以看到,standby已经同步看到了新加的数据文件。

以上是standby_file_management为AUTO的方式,另外,如果数据文件在裸设备下面,一定要先在主库端和备库端都建立好裸设备,尤其是备库端,不然备库端无法找到相应的裸设备就会出问题。如果不是AUTO而是MANUAL,则需要手动的在主库和备库端同时修改。

我的<>,欢迎观看: http://edu.51cto.com/lecturer/index/user_id-1850277.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17172228/viewspace-1077956/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-07-21

  • 博文量
    120
  • 访问量
    846377