一、rac 環境修改spfile位置

1. 從集群的任意一個實例登錄，查看spfile信息

SQL> show parameter spfile
NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
spfile                   string  +DATA/racdb/spfileracdb.ora'

2. 重新創建新的spfile

SQL>  create pfile = '/home/oracle/pfile.ora' from spfile;
File created.
SQL>  create spfile='+NEW_DATA' from pfile = '/home/oracle/pfile.ora';
File created.

3. 通過ASM命令行，查看spfile，并賦予別名。

ASMCMD> pwd
+new_data/racdb/PARAMETERFILE
ASMCMD> ls
spfileracdb.ora.267.1085996147
ASMCMD> mkalias +new_data/racdb/PARAMETERFILE/spfileracdb.ora.267.1085996147 +NEW_DATA/racdb/spfileracdb.ora

4. 修改注冊新的spfile文件：

$ srvctl modify database -d racdb-p +NEW_DATA/racdb/spfileracdb.ora

5. 重啟數據庫

$ srvctl stop database -d racdb
$ srvctl start database -d racdb

6. 查看結果

SQL> show parameter spfile
NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
spfile                   string  +NEW_DATA/racdb/spfileracdb.ora

7. 查看參數文件

cat initracdb2.ora
SPFILE='+NEW_DATA/racdb/spfileracdb.ora'            # line added by Agent

二、控制文件路徑修改

1、查看控制文件路徑：

SQL>col NAME for a50
SQL> select status, name from  v$controlfile;
STATUS  NAME
------- --------------------------------------------------
        +DATA/racdb/control01.ctl
        +DATA/racdb/control02.ctl
 或
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/racdb/control01.ctl, +DATA/racdb/control02.ctl

2、關閉數據庫所有實例，啟動一個實例到nomount，用rman備份控制文件

SQL>startup nomount; -- =>啟庫為nomount 
RMAN>  restore controlfile to '+NEW_DATA/racdb/control01.ctl'  from '+DATA/racdb/control01.ctl';
Starting restore at 15-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=667 instance=racdb3 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 15-OCT-21
RMAN>  restore controlfile to '+NEW_DATA/racdb/control02.ctl'  from '+DATA/racdb/control02.ctl';
Starting restore at 15-OCT-21
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 15-OCT-21

3、復制控制文件使用：

ASMCMD> cp +NEW_DATA/racdb/CONTROLFILE/current.267.1086009349 control01.ctl
copying +NEW_DATA/racdb/CONTROLFILE/current.267.1086009349 -> +new_data/racdb/control01.ctl
ASMCMD> cp  +NEW_DATA/racdb/CONTROLFILE/current.262.1086009391 control02.ctl
copying +NEW_DATA/racdb/CONTROLFILE/current.262.1086009391 -> +new_data/racdb/control02.ctl

4、修改參數

SQL> alter system set control_files='+new_data/racdb/control01.ctl', '+new_data/racdb/control02.ctl' scope=spfile sid='*';
System altered.

三、數據庫表空間遷移

1、表空間遷移：

– 數據庫系統表空間

 RMAN> backup as copy tablespace SYSTEM format '+NEW_DATA';
 RMAN> switch tablespace SYSTEM to copy;
 RMAN> backup as copy tablespace SYSAUX format '+NEW_DATA';
 RMAN> switch tablespace SYSAUX to copy;
 RMAN> backup as copy tablespace USERS format '+NEW_DATA';
 RMAN> switch tablespace USERS to copy; 

– 業務用戶表空間

 RMAN> backup as copy tablespace tworain_DAT format '+NEW_DATA';
 RMAN> switch tablespace tworain_DAT  to copy;

– undo 表空間：

 RMAN> backup as copy tablespace undotbs001 format '+NEW_DATA';
 RMAN> switch tablespace undotbs001  to copy;

2、驗證：

SQL> select file_name from dba_data_files where tablespace_name ='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
+NEW_DATA/racdb/datafile/system.261.1086019835

SQL> select file_name from dba_data_files where tablespace_name ='SYSAUX';
FILE_NAME
--------------------------------------------------------------------------------
+NEW_DATA/racdb/datafile/sysaux.260.1086019883

SQL> select file_name from dba_data_files where tablespace_name ='USERS';
FILE_NAME
--------------------------------------------------------------------------------
+NEW_DATA/racdb/datafile/users.312.1086020021

3、temp 表空間，修改新路徑：

SQL> select name from v$tempfile;
 
NAME
--------------------------------------------------------------------------------
+DATA/racdb/temp01.dbf
+DATA/racdb/temp02.dbf

4、 創建新temp 表空間

SQL> create temporary tablespace temp03 tempfile '+NEW_DATA' size 30M autoextend on;
 
Tablespace created.
 
SQL> select name from v$tempfile;
 
NAME
--------------------------------------------------------------------------------
+DATA/test/temp01.dbf
+DATA/test/temp02.dbf
+NEW_DATA/test/tempfile/temp03.311.1084007975

5、修改默認表空間：

SQL> alter database default temporary tablespace temp03;

Database altered.

6、 刪除舊表空間：

SQL>  drop tablespace temp including contents and datafiles;

四、redo 文件遷移

1、 創建新redo:

ALTER DATABASE ADD LOGFILE THREAD 1  GROUP 1 ('+new_data') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 1  GROUP 2 ('+new_data') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 2  GROUP 11 ('+new_data') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 2  GROUP 12 ('+new_data') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 3  GROUP 21 ('+new_data') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 3  GROUP 22 ('+new_data') SIZE 500M;

ALTER DATABASE ADD LOGFILE THREAD 4  GROUP 31 ('+new_data') SIZE 500M;
ALTER DATABASE ADD LOGFILE THREAD 4  GROUP 32 ('+new_data') SIZE 500M;

2、 查看

select g.GROUP#, 
g.STATUS, 
l.MEMBER
 from v$log g
left join v$logfile l 
on g.GROUP#=l.GROUP#
where g.STATUS = 'INACTIVE' 
and l.MEMBER like '+DATA%' order by 2;
    GROUP# STATUS           MEMBER
---------- ---------------- ----------------------------------------------------------------------
        16 INACTIVE         +DATA/racdb/onlinelog/redo16.log
        17 INACTIVE         +DATA/racdb/onlinelog/redo17.log
        18 INACTIVE         +DATA/racdb/onlinelog/redo18.log
        19 INACTIVE         +DATA/racdb/onlinelog/redo19.log
        20 INACTIVE         +DATA/racdb/onlinelog/redo20.log
        21 INACTIVE         +DATA/racdb/onlinelog/redo21.log

3、 刪除：

alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;

五、數據文件遷移

SQL> select FILE_ID,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME ='racdb_DAT';
FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
         6 +NEW_DATA/racdb/datafile/racdb_dat.265.1083955823
        10 +NEW_DATA/racdb/datafile/racdb_dat.262.1083956499
        11 +NEW_DATA/racdb/datafile/racdb_dat.261.1083956723
        21 +NEW_DATA/racdb/datafile/racdb_dat.267.1083955373
        22 +NEW_DATA/racdb/datafile/racdb_dat.301.1083964647
        23 +NEW_DATA/racdb/datafile/racdb_dat.268.1083958067
        24 +NEW_DATA/racdb/datafile/racdb_dat.269.1083958291

– rman 復制數據文件 ：

 copy datafile 10 to '+DATA';
 copy datafile 11 to '+DATA';
 copy datafile 21 to '+DATA';

– 修改控制文件，數據文件路徑：

 alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.265.1083955823' to '+DATA/racdb/datafile/racdb_dat.334.1085682389';
 alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.262.1083956499' to '+DATA/racdb/datafile/racdb_dat.333.1085682705';
 alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.261.1083956723' to '+DATA/racdb/datafile/racdb_dat.332.1085682719';
 alter database rename file '+NEW_DATA/racdb/datafile/racdb_dat.267.1083955373' to '+DATA/racdb/datafile/racdb_dat.296.1085682739';

?????????????????????????文章推薦