oracle rac 主库,oracle11gRAC+DG主库增加数据文件

oracle11gRAC+DG主库增加数据文件

oracle11gRAC+DG主库增加数据文件

# 环境

操作系统

$ uname -a

Linux jinhu-db02 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

$ cat /etc/redhat-release

CentOS release 6.9 (Final)

数据库

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

查看用户的默认表空间

set linesize 200

SELECT username,default_tablespace FROM dba_users WHERE account_status = 'OPEN' ORDER BY 1;

USERNAME        DEFAULT_TABLESPACE

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

A_ONLINEPAY        TBS_PROENV

DBSNMP        SYSAUX

MGMT_VIEW        SYSTEM

PROENV_PREPAID        USERS

SYS        SYSTEM

SYSMAN        SYSAUX

SYSTEM        SYSTEM

ZABBIX        TBS_ZABBIX_01

查看表空间大小及使用情况

SELECT a.tablespace_name tbs_name, total/(1024*1024) total_mb,

free/(1024*1024) free_mb, (total-free)/(1024*1024) used_mb,

round((total-free)/total,4)*100 used_pct,

round(free/total,4)*100 free_pct

FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name;

TBS_NAME TOTAL_MB    FREE_MB USED_MB   USED_PCT   FREE_PCT

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

SYSAUX      1740      88.75 1651.25       94.9   5.1

UNDOTBS1       100     83.875 16.125      16.13 83.88

USERS 5     3.0625 1.9375      38.75 61.25

SYSTEM      1010     244.25 765.75      75.82 24.18

TBS_ZABBIX_01       100   99       1 1    99

UNDOTBS2      4475    4397.75   77.25       1.73 98.27

TBS_PROENV      5120   4695.375 424.625       8.29 91.71

查看表空间的数据文件及是否打开自动扩展

col file_name for a80

col tablespace_name for a20

col autoextensible for a20

SELECT tablespace_name, file_id, file_name, round(bytes/(1024*1024), 0) total_mb, autoextensible

FROM dba_data_files

ORDER BY 2;

TABLESPACE_NAME FILE_ID FILE_NAME    TOTAL_MB AUTOEXTENSIBLE

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

SYSTEM       1 +RACDATA/orcl/datafile/system.256.969659767        1010 YES

SYSAUX       2 +RACDATA/orcl/datafile/sysaux.257.969659769        1740 YES

UNDOTBS1       3 +RACDATA/orcl/datafile/undotbs1.258.969659769 100 YES

USERS       4 +RACDATA/orcl/datafile/users.259.969659769   5 YES

UNDOTBS2       5 +RACDATA/orcl/datafile/undotbs2.267.969659929        4475 YES

TBS_PROENV       6 +RACDATA/orcl/datafile/tbs_proenv01.dbf        5120 NO

TBS_ZABBIX_01       7 +RACDATA/orcl/datafile/tbs_zabbix_01.303.972403233 100 YES

查看某张表属于哪个表空间

SELECT owner,table_name,tablespace_name

FROM dba_tables

WHERE table_name = 'TRAN_HISTRAN';

OWNER        TABLE_NAME       TABLESPACE_NAME

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

PROENV_PREPAID        TRAN_HISTRAN       TBS_PROENV

给表空间增加数据文件

20180415 15:25-15:34

ALTER TABLESPACE users ADD DATAFILE '+RACDATA' SIZE 1G AUTOEXTEND ON NEXT 100M;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER TABLESPACE sysaux ADD DATAFILE '+RACDATA' SIZE 2G AUTOEXTEND ON NEXT 100M;

ALTER TABLESPACE tbs_proenv ADD DATAFILE '+RACDATA' SIZE 5G AUTOEXTEND ON NEXT 100M;

ALTER SYSTEM ARCHIVE LOG CURRENT;

查看表空间大小及使用情况

SELECT a.tablespace_name tbs_name, total/(1024*1024) total_mb,

free/(1024*1024) free_mb, (total-free)/(1024*1024) used_mb,

round((total-free)/total,4)*100 used_pct,

round(free/total,4)*100 free_pct

FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name;

TBS_NAME TOTAL_MB    FREE_MB USED_MB   USED_PCT   FREE_PCT

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

SYSAUX      3788    2133.75 1654.25      43.67 56.33

UNDOTBS1       100      84.25   15.75      15.75 84.25

USERS      1029  1026.0625 2.9375        .29 99.71

SYSTEM      1010     244.25 765.75      75.82 24.18

TBS_ZABBIX_01       100   99       1 1    99

UNDOTBS2      4475    4398.75   76.25        1.7 98.3

TBS_PROENV     10240   9814.375 425.625       4.16 95.84

SELECT tablespace_name, file_id, file_name, round(bytes/(1024*1024), 0) total_mb, autoextensible

FROM dba_data_files

ORDER BY 2;

TABLESPACE_NAME FILE_ID FILE_NAME      TOTAL_MB AUTOEXTENSIBLE

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

SYSTEM       1 +RACDATA/orcl/datafile/system.256.969659767 1010 YES

SYSAUX       2 +RACDATA/orcl/datafile/sysaux.257.969659769 1740 YES

UNDOTBS1       3 +RACDATA/orcl/datafile/undotbs1.258.969659769   100 YES

USERS       4 +RACDATA/orcl/datafile/users.259.969659769     5 YES

UNDOTBS2       5 +RACDATA/orcl/datafile/undotbs2.267.969659929 4475 YES

TBS_PROENV       6 +RACDATA/orcl/datafile/tbs_proenv01.dbf 5120 NO

TBS_ZABBIX_01       7 +RACDATA/orcl/datafile/tbs_zabbix_01.303.972403233   100 YES

USERS       8 +RACDATA/orcl/datafile/users.854.973524341 1024 YES

SYSAUX       9 +RACDATA/orcl/datafile/sysaux.857.973524697 2048 YES

TBS_PROENV      10 +RACDATA/orcl/datafile/tbs_proenv.856.973524723 5120 YES

查看物理备库日志应用情况

SELECT sequence#,archived,applied

FROM v$archived_log

ORDER BY 1;

!!The End!!