oracle11gRAC+DG主库增加数据文件

发布时间：2020-12-12 13:40:55 所属栏目：百科来源：网络整理

导读：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 rele

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,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,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!!

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!