rac修改表空间数据文件,控制文件,日志组.txt

目前先提供手动增加表空间，重做日志组，以及控制文件的方法，C03会集成到自动化脚本中，请用sys用户dba权限执行SQL命令

--增加SYSTEM数据文件

SQL> ALTER TABLESPACE SYSTEM ADD DATAFILE '+DG_ORA/ipcc/ora_system.dbf' SIZE 8008M;

--增加SYSAUX数据文件

SQL> ALTER TABLESPACE SYSAUX ADD DATAFILE '+DG_ORA/ipcc/ora_sysaux01.dbf' SIZE 8008M;
SQL> ALTER TABLESPACE SYSAUX ADD DATAFILE '+DG_ORA/ipcc/ora_sysaux02.dbf' SIZE 8008M;
SQL> ALTER TABLESPACE SYSAUX ADD DATAFILE '+DG_ORA/ipcc/ora_sysaux03.dbf' SIZE 8008M;

--增加UNDOTBS数据文件
SQL> ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DG_ORA/ipcc/ora_rbs01.dbf' SIZE 8008M;
SQL> ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DG_ORA/ipcc/ora_rbs02.dbf' SIZE 8008M;
SQL> ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '+DG_ORA/ipcc/ora_rbs03.dbf' SIZE 8008M;
SQL> ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '+DG_ORA/ipcc/ora_rbs04.dbf' SIZE 8008M;

--增加TMP表空间

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DG_ORA/ipcc/ora_temp01.dbf' SIZE 8008M;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DG_ORA/ipcc/ora_temp02.dbf' SIZE 8008M;

--增加user数据文件

SQL> ALTER TABLESPACE users ADD DATAFILE '+DG_ORA/ipcc/ora_user.dbf' SIZE 48M;

--增加index数据文件

SQL> CREATE TABLESPACE INDEXS DATAFILE '+DG_ORA/ipcc/ora_index.dbf' SIZE 48M;
 
--增加tools数据文件
 
SQL> CREATE TABLESPACE TOOLS DATAFILE '+DG_ORA/ipcc/ora_tools.dbf' SIZE 48M;

--校验TMP表空间数据文件

SQL> select NAME from v_$tempfile;

NAME
--------------------------------------------------------------------------------
+DG_ORA/ipcc/ora_temp01
+DG_ORA/ipcc/ora_temp02.dbf
+DG_ORA/ipcc/ora_temp01.dbf

--校验数据文件

SQL> select NAME from v$datafile;

NAME
--------------------------------------------------------------------------------
+DG_ORA/ipcc/ora_system
+DG_ORA/ipcc/ora_sysaux01
+DG_ORA/ipcc/ora_rbs01
+DG_ORA/ipcc/ora_rbs02
+DG_ORA/ipcc/ora_user
+DG_ORA/ipcc/ora_system.dbf
+DG_ORA/ipcc/ora_sysaux01.dbf
+DG_ORA/ipcc/ora_sysaux02.dbf
+DG_ORA/ipcc/ora_sysaux03.dbf
+DG_ORA/ipcc/ora_rbs01.dbf
+DG_ORA/ipcc/ora_rbs02.dbf
+DG_ORA/ipcc/ora_rbs03.dbf
+DG_ORA/ipcc/ora_rbs04.dbf
+DG_ORA/ipcc/ora_user.dbf
+DG_ORA/ipcc/ora_index.dbf

NAME
--------------------------------------------------------------------------------
+DG_ORA/ipcc/ora_tools.dbf

--增加控制文件

目前控制文件只有两个，为了安全性，计划增加到3个

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------

+DG_ORA/ipcc/ora_ctl01
+DG_ORA/ipcc/ora_ctl02

1.执行如下sql

alter system set control_files = '+DG_ORA/ipcc/ora_ctl01.ctl','+DG_ORA/ipcc/ora_ctl02.ctl','+DG_ORA/ipcc/ora_ctl03.ctl' scope=spfile;

2.停止数据库

[grid@dbfs1 ~]$ srvctl stop database -d ipcc

3.复制控制文件

[grid@dbfs1 ~]su - grid
[grid@dbfs1 ~]asmcmd -p
ASMCMD [+] > cd dg_ora
ASMCMD [+dg_ora] > cd ipcc
ASMCMD [+dg_ora/ipcc] > cp ora_ctl01 ora_ctl01.ctl
ASMCMD [+dg_ora/ipcc] > cp ora_ctl01 ora_ctl02.ctl
ASMCMD [+dg_ora/ipcc] > cp ora_ctl01 ora_ctl03.ctl

4.重新启动数据库

[grid@dbfs1 ~]$ srvctl start database -d ipcc

5.验证结果

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DG_ORA/ipcc/ora_ctl01.ctl
+DG_ORA/ipcc/ora_ctl02.ctl
+DG_ORA/ipcc/ora_ctl03.ctl

--增加重做日志组文件
 
(主节点oracle用户sqlplus下执行)
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('+DG_ORA/ipcc/ora_redo05_1.log','+DG_ORA/ipcc/ora_redo05_2.log')SIZE 508M;

(主节点grid用户执行)
srvctl stop instance -d ipcc -i ipcc1

(备节点oracle用户sqlplus下执行)

SQL> alter database disable thread 1;
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DG_ORA/ipcc/ora_redo01_1.log','+DG_ORA/ipcc/ora_redo01_2.log')SIZE 508M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DG_ORA/ipcc/ora_redo02_1.log','+DG_ORA/ipcc/ora_redo02_2.log')SIZE 508M;
SQL> alter database enable thread 1;

(主节点grid用户执行)
srvctl start instance -d ipcc -i ipcc1
srvctl stop instance -d ipcc -i ipcc2
(主节点oracle用户sqlplus下执行)
SQL> alter database disable thread 2;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ('+DG_ORA/ipcc/ora_redo03_1.log','+DG_ORA/ipcc/ora_redo03_2.log')SIZE 508M;
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DG_ORA/ipcc/ora_redo04_1.log','+DG_ORA/ipcc/ora_redo04_2.log')SIZE 508M;
SQL> alter database enable thread 2;
(主节点grid用户执行)
srvctl start instance -d ipcc -i ipcc2

验证结果

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DG_ORA/ipcc/ora_redo01_1.log
+DG_ORA/ipcc/ora_redo01_2.log
+DG_ORA/ipcc/ora_redo02_1.log
+DG_ORA/ipcc/ora_redo02_2.log
+DG_ORA/ipcc/ora_redo03_1.log
+DG_ORA/ipcc/ora_redo03_2.log
+DG_ORA/ipcc/ora_redo04_1.log
+DG_ORA/ipcc/ora_redo04_2.log
+DG_ORA/ipcc/ora_redo05_1.log
+DG_ORA/ipcc/ora_redo05_2.log

至此表空间，数据文件，控制文件，日志组已经更新完毕