Oracle物理迁移_oracle 移动数据文件-优快云博客

本文链接：https://blog.youkuaiyun.com/cmx996007/article/details/135087778

在oracle中移动数据文件 --oracle数据库由数据文件，控制文件和联机日志文件三种文件组成。 --由于磁盘空间的变化，或基于数据库磁盘io性能的调整等，我们可能会考虑移动数据库文件，下面以linux平台为例，分别讨论三种数据库文件的移动方法。

一、移动数据文件

-- 可以用ALTER DATABASE，ALTER TABLESPACE两种方法移动数据文件。

ALTER DATABASE方法

-- 用此方法，可以移动任何表空间的数据文件

1.关闭数据库
[oracle@drcmx ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit;

2.检查数据库是否关闭
[oracle@drcmx ~]$ ps -ef |grep pmon
oracle     9805   3358  0 18:00 pts/1    00:00:00 grep --color=auto pmon

[oracle@drcmx ~]$ ss -lntup |grep 1521
tcp    LISTEN     0      128    [::]:1521               [::]:*                   users:(("tnslsnr",pid=9329,fd=11))


3.用操作系统命令移动数据文件：
-- 将数据文件 'test.ora' 从/ora/oracle/data1目录移动到/ora/oracle/data2目录下：
[oracle@drcmx ~]$ mv /ora/oracle/data1/test.ora /ora/oracle/data2

4. Mount数据库，用ALTER DATABASE命令将数据文件改名：
$ sqlplus /nolog
SQL> CONNECT INTERNAL;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME FILE '/ora/oracle/data1/test.ora' TO '/ora/oracle/data2/test.ora';

5. 打开数据库：
SQL> ALTER DATABASE OPEN;
SQL>SELECT NAME,STATUS FROM V$DATAFILE；

ALTER TABLESPACE方法

- 用此方法，要求此数据文件既不属于SYSTEM表空间，也不属于含有ACTIVE回滚段或临时段的表空间。

STEP1. 将此数据文件所在的表空间OFFLINE：
$ sqlplus /nolog
SQL> CONNECT INTERNAL;
SQL> ALTER TABLESPACE test OFFLINE;
SQL> EXIT;

STEP2. 用操作系统命令移动数据文件：
将数据文件 'test.ora' 从/ora/oracle/
data1目录移动到/ora/oracle/data2目录下：
$ mv /ora/oracle/data1/test.ora /ora/oracle/data2

STEP3. 用ALTER TABLESPACE命令改数据文件名：
$ sqlplus /nolog
SQL> CONNECT INTERNAL;
SQL> ALTER TABLESPACE test RENAME DATAFILE '/ora/oracle/data1/test.ora' TO '/ora/oracle/data2/test.ora';

STEP4. 将此数据文件所在的表空间ONLINE：
SQL> ALTER TABLESPACE test ONLINE;
SQL> SELECT NAME,STATUS FROM V$DATAFILE；

二. 移动控制文件

-- 控制文件 在 INIT.ORA文件中指定。移动控制文件相对比较简单，下数据库，
-- 编辑INIT.ORA，移动控制文件，重启动数据库。

STEP 1. 下数据库：
$ sqlplus /nolog
SQL> CONNECT INTERNAL;
SQL> SHUTDOWN;
SQL> EXIT;

STEP 2.用操作系统命令 移动控制文件：
--将控制文件'ctl3orcl.ora' 从/ora/oracle/data1目录移动到/ora/oracle/data2目录下：
$ mv /ora/oracle/data1/ctrlorcl3.ora /ora/oracle/data2

STEP 3. 编辑INIT.ORA文件：
INIT.ORA文件的在$ORACLE_HOME/dbs目录下，
修改参数 "control_files"，其中指定移动后的控制文件：
control_files = (/ora/oracle/data1/ctrlorcl1.ora,/ora/oracle/data1/ctrlorcl2.ora,/ora/oracle/data2/ctrlorcl3.ora)

STEP 4. 重启动数据库：
$ sqlplus /nolog
SQL> CONNECT INTERNAL;
SQL> STARTUP;
SQL>SELECT name FROM V$CONTROLFILE;
SQL> EXIT;

三. 移动联机日志文件

STEP 1. 停数据库：
$ sqlplus /nolog
SQL> CONNECT INTERNAL;
SQL> SHUTDOWN;
SQL> EXIT;

STEP 2. 用操作系统命令移动联机日志文件：
--将联机日志文件'redolog1.ora' 从/ora/oracle/data1目录移动到/ora/oracle/data2目录下：
$ mv /ora/oracle/data1/redolog1.ora /ora/oracle/data2

STEP 3. Mount数据库，用ALTER DATABASE 命令改联机日志文件名：.
$ sqlplus /nolog
SQL> CONNECT INTERNAL;
SQL> STARTUP MOUNT ;
SQL> ALTER DATABASE RENAME FILE '/ora/oracle/data1/redolog1.ora' TO '/ora/oracle/data2/redolog1.ora';

STEP 4.重启动数据库： .
SQL> ALTER DATABASE OPEN;
SQL>SELECT MEMBER FROM V$LOGFILE；

四、整个数据库迁移

参考文章：oracle19c单机修改DB_NAME INSTANCE_NAME、PDB_oracle修改db_unique_name-优快云博客

4.1 数据库name查看


1.源端
SQL> show parameter name;  

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name           string
db_file_name_convert             string
db_name                  string  cmx
db_unique_name               string  cmx
global_names                 boolean     FALSE
instance_name                string  cmx
lock_name_space              string
log_file_name_convert            string
processor_group_name             string
service_names                string  cmx

###记录当前数据库dbid和name
select dbid,name from v$database;

2.目标端
SQL> show parameter name;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name           string
db_file_name_convert             string
db_name                  string  drcmx
db_unique_name               string  drcmx
global_names                 boolean     FALSE
instance_name                string  drcmx
lock_name_space              string
log_file_name_convert            string
processor_group_name             string
service_names                string  drcmx

###记录当前数据库dbid和name
select dbid,name from v$database;

4.2 进行迁移

本机进行数据目录的移动
1.源端准备用于启动的参数文件
[oracle@drcmx ~]$ sqlplus / as sysdba
SQL> create pfile='/oradata/cmx/init1217.ora' from spfile;
SQL> exit;

[oracle@drcmx ~]$ ll /oradata/init1217.ora
-rw-r--r-- 1 oracle oinstall 900 Dec 17 18:26 /oradata/cmx/init1217.ora

2.源端和目标端关闭数据库
[oracle@drcmx ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit;

3.检查数据库是否关闭
[oracle@drcmx ~]$ ps -ef |grep pmon
oracle     9805   3358  0 18:00 pts/1    00:00:00 grep --color=auto pmon

[oracle@drcmx ~]$ ss -lntup |grep 1521
tcp    LISTEN     0      128    [::]:1521               [::]:*                   users:(("tnslsnr",pid=9329,fd=11))

4.将目标端将数据文件和参数文件备份
[oracle@drcmx /oradata]$ mv drcmx/  drcmx_bak/
[oracle@drcmx /oradata]$ ll
total 4
drwxr-x--- 2 oracle oinstall 179 Dec 17 17:39 drcmx_bak
-rw-r--r-- 1 oracle oinstall 901 Dec 17 18:30 init1217.ora

cd $ORACLE_HOME/dbs
mv spfiledrcmx.ora   spfiledrcmxbak.ora


5.传输源端数据库到目标端
[oracle@cmx /oradata]$ scp -r /oradata/* oracle@10.0.0.213:/oradata
oracle@10.0.0.213's password: 


6.目标端检查是否传输过来，并查看参数文件的最新位置
[oracle@drcmx /oradata]$ ll
total 0
drwxr-x--- 2 oracle oinstall 199 Dec 17 19:19 cmx
drwxr-x--- 2 oracle oinstall 179 Dec 17 17:39 drcmx_bak

[oracle@drcmx /oradata]$ ll cmx
total 1549672
-rw-r----- 1 oracle oinstall   9748480 Dec 17 19:19 control01.ctl
-rw-r--r-- 1 oracle oinstall       871 Dec 17 19:19 init1217.ora ##参数文件
-rw-r----- 1 oracle oinstall  52429312 Dec 17 19:19 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Dec 17 19:19 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Dec 17 19:19 redo03.log
-rw-r----- 1 oracle oinstall 534781952 Dec 17 19:19 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Dec 17 19:19 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Dec 17 19:19 temp01.dbf
-rw-r----- 1 oracle oinstall  73408512 Dec 17 19:19 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Dec 17 19:19 users01.dbf

7.查看参数文件中各文件的位置，是否有未创建的
[oracle@drcmx ~]$ cat   /oradata/cmx/init1217.ora
*.control_files='/oradata/cmx/control01.ctl','/oracle/dbsoft/fast_recovery_area/cmx/control02.ctl'
查看两个控制文件是否存在，不存在传输过来，并改变位置

7.1查看01控制文件是否存在，不存在手动创建
[oracle@drcmx /oracle/dbsoft/diag/rdbms/cmx/cmx/trace]$ ll /oradata/cmx/control01.ctl   ###已存在，则无需变动
-rw-r----- 1 oracle oinstall 9748480 Dec 17 19:37 /oradata/cmx/control01.ctl

7.2查看02控制文件是否存在，不存在手动创建 
[oracle@drcmx /oracle/dbsoft/diag/rdbms/cmx/cmx/trace]$ ll /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl
ls: cannot access /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl: No such file or directory  ###文件不存在，需要传输到目标端

准备目录
mkdir -p /oracle/dbsoft/fast_recovery_area/cmx/

源端进行文件传输
[oracle@cmx ~]$ scp -r /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl oracle@10.0.0.213:/oracle/dbsoft/fast_recovery_area/cmx/
oracle@10.0.0.213's password: 
control02.ctl    

目标端查看
[oracle@drcmx /oracle/dbsoft/diag/rdbms/cmx/cmx/trace]$ ll /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl
-rw-r----- 1 oracle oinstall 9748480 Dec 17 19:50 /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl


7.3参数文件中，下方目录需要在目标端重新创建
mkdir -p /oracle/dbsoft/admin/cmx/adump


8.修改环境变量
[oracle@drcmx ~]$ cd 
[oracle@drcmx ~]$ vi .bash_profile
[oracle@drcmx ~]$ cat .bash_profile 
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup program
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/oracle/dbsoft/
export ORACLE_HOME=/oracle/dbsoft/product/ora11g
export ORACLE_SID=cmx  #修改
export ORACLE_TERM=xterm 
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS";
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
PATH=$PATH:$HOME/bin
export PATH

[oracle@drcmx ~]$ source .bash_profile 
[oracle@drcmx ~]$ echo $ORACLE_SID 
cmx


9.启动数据库
sqlplus / as sysdba
startup nomount pfile=/oradata/cmx/init1217.ora
    
启动后nomount状态后
    create spfile from pfile='/oradata/cmx/init1217.ora';
    shutdown immediate;
    startup;
    
10.检查数据库状态
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter name;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name           string
db_file_name_convert             string
db_name                  string  cmx
db_unique_name               string  cmx
global_names                 boolean     FALSE
instance_name                string  cmx
lock_name_space              string
log_file_name_convert            string
processor_group_name             string
service_names                string  cmx
SQL> 

11.至此，数据库物理克隆成功

12.检查文件的位置
SQL> select name from v$datafile;  ###数据文件
SQL> select name from v$tempfile;  ###临时文件
SQL> select member from v$logfile; ###日志文件

4.3 修改数据库db_name

1 记录当前数据库dbid和name
SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
1078390580 CMX



2 修改dbname
shutdown immediate; 
startup mount; --nid需要在mount状态下才能做
SQL> !nid target=sys/oracle dbname=drcmx   ###填写为要修改的

DBNEWID: Release 11.2.0.4.0 - Production on Sun Dec 17 20:02:26 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database CMX (DBID=1078390580)

Connected to server version 11.2.0

Control Files in database:
    /oradata/cmx/control01.ctl
    /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl

Change database ID and database name CMX to DRCMX? (Y/[N]) =>   ##确认是否要修改
Proceeding with operation
Changing database ID from 1078390580 to 3377472210
Changing database name from CMX to DRCMX
    Control File /oradata/cmx/control01.ctl - modified
    Control File /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl - modified
    Datafile /oradata/cmx/system01.db - dbid changed, wrote new name
    Datafile /oradata/cmx/sysaux01.db - dbid changed, wrote new name
    Datafile /oradata/cmx/undotbs01.db - dbid changed, wrote new name
    Datafile /oradata/cmx/users01.db - dbid changed, wrote new name
    Datafile /oradata/cmx/temp01.db - dbid changed, wrote new name
    Control File /oradata/cmx/control01.ctl - dbid changed, wrote new name
    Control File /oracle/dbsoft/fast_recovery_area/cmx/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to DRCMX.
Modify parameter file and generate a new password file before restarting.
Database ID for database DRCMX changed to 3377472210.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

3.修改环境变量中的ORACLE_SID
[oracle@drcmx ~]$ echo $ORACLE_SID 
cmx

[oracle@drcmx ~]$ vi  .bash_profile 
export ORACLE_SID=drcmx  ###将SID修改为最新的db_name

[oracle@drcmx ~]$ source  .bash_profile 
[oracle@drcmx ~]$ echo $ORACLE_SID 
drcmx


4.修改参数文件中dbname
[oracle@drcmx ~]$ sqlplus / as sysdba
SQL> startup mount;
ORA-01103: database name ‘YZCDB’ in control file is not ‘CFCDB’
提示控制文件中的数据库名与实际的数据库名不匹配
执行下方命令，修改spfile
SQL> alter system set db_name=drcmx scope=spfile;

System altered.


5.关闭数据库，重新启动
SQL> shutdown immediate;
SQL> startup mount;
SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
3377472210 DRCMX
SQL> alter database open resetlogs;

SQL> show parameter name;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name           string
db_file_name_convert             string
db_name                  string  DRCMX
db_unique_name               string  DRCMX
global_names                 boolean     FALSE
instance_name                string  drcmx   ###检查实例名是否被修改，未修改使用4.4章节修改
lock_name_space              string
log_file_name_convert            string
processor_group_name             string
service_names                string  DRCMX

4.4修改instance_name

1.停库
SQL> shutdown immediate;


2.修改环境变量中的ORACLE_SID
[oracle@drcmx ~]$ echo $ORACLE_SID 
cmx

[oracle@drcmx ~]$ vi  .bash_profile 
export ORACLE_SID=drcmx  ###将SID修改为最新的db_name

[oracle@drcmx ~]$ source  .bash_profile 
[oracle@drcmx ~]$ echo $ORACLE_SID 
drcmx


3.重命名spfile文件名
[oracle@drcmx ~]$ cd $ORACLE_HOME/dbs
[oracle@ora-recover01 dbs]$ mv spfilecmx.ora spfiledrcmx.ora
[oracle@drcmx /oracle/dbsoft/product/ora11g/dbs]$ ll
total 28
-rw-rw---- 1 oracle oinstall 1544 Dec 17 20:03 hc_cmx.dat
-rw-rw---- 1 oracle oinstall 1544 Dec 17 20:24 hc_drcmx.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall   24 Dec 17 19:31 lkCMX
-rw-r----- 1 oracle oinstall   24 Dec 17 17:39 lkDRCMX
-rw-r----- 1 oracle oinstall 1536 Dec 17 17:40 orapwdrcmx
-rw-r----- 1 oracle oinstall 2560 Dec 17 20:02 spfiledrcmx.ora


 4.启动数据库
 [oracle@drcmx ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 17 20:34:42 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;

4.5 重新注册监听

pdb无法连接时，使用下方命令，将服务名与pdb绑定
sqlplus / as sysdba
SQL> alter system set service_names=srvi17thduath,PDBI17UAT scope=both;
SQL> alter system register;

System altered.

SQL> host lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-DEC-2023 20:39:18

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

SQL> host lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-DEC-2023 20:39:29

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-DEC-2023 17:53:15
Uptime                    0 days 2 hr. 46 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/dbsoft/product/ora11g/network/admin/listener.ora
Listener Log File         /oracle/dbsoft/diag/tnslsnr/drcmx/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=drcmx)(PORT=1521)))
Services Summary...
Service "cmxXDB" has 1 instance(s).
  Instance "drcmx", status READY, has 1 handler(s) for this service...
Service "drcmx" has 1 instance(s).
  Instance "drcmx", status READY, has 1 handler(s) for this service...
The command completed successfully

注意：如果listener.ora、 tnsnames.ora文件中含有原来的 SERVICE_NAME 名称的话，用编辑器做相应的修改。针对数据库自启动文件： /etc/oratab , /etc/rc.local 文件也需要做相应的修改。

在pdb中新添加服务名
alter session set container = PDBI17UAT;
SQL> exec dbms_service.create_service('SRVI17THDCLONEHAP', 'SRVI17THDCLONEHAP');
SQL> exec dbms_service.start_service('SRVI17THDCLONEHAP');

4.6 传输密码文件到目标端


1.源端寻找密码文件
[oracle@cmx /oracle/dbsoft/product/ora11g/dbs]$ cd 
[oracle@cmx ~]$ cd $ORACLE_HOME/dbs
[oracle@cmx /oracle/dbsoft/product/ora11g/dbs]$ ls
hc_cmx.dat  init.ora  lkCMX  orapwcmx  spfilecmx.ora
[oracle@cmx /oracle/dbsoft/product/ora11g/dbs]$ ll /oracle/dbsoft/product/ora11g/dbs/orapwcmx
-rw-r----- 1 oracle oinstall 1536 Dec 17 17:18 /oracle/dbsoft/product/ora11g/dbs/orapwcmx

2.传输密码文件
[oracle@cmx /oracle/dbsoft/product/ora11g/dbs]$ scp  /oracle/dbsoft/product/ora11g/dbs/orapwcmx oracle@10.0.0.213:/oracle/dbsoft/product/ora11g/dbs/
oracle@10.0.0.213's password: 
orapwcmx                                                                   100% 1536     1.2MB/s   00:00   

3.目标端检查是否传输完成
[oracle@drcmx ~]$ cd $ORACLE_HOME/dbs
[oracle@drcmx /oracle/dbsoft/product/ora11g/dbs]$ ls
hc_cmx.dat  hc_drcmx.dat  init.ora  lkCMX  lkDRCMX  orapwcmx  orapwdrcmx  spfiledrcmx.ora

4.将密码文件重命名
cp  orapwcmx  orapwdrcmx(示例：orapw+$ORACLE_SID)
[oracle@drcmx /oracle/dbsoft/product/ora11g/dbs]$ cp  orapwcmx  orapwdrcmx

4.7 PDB 修改名称

1.防止修改用户名密码后应用一直在发起错误连接，可事先查询哪个IP在连接数据库，然后断开对应连接即可(这里需用到触发器记录客户端IP)**
select username,machine,terminal,program,client_info,logon_time from v$session order by logon_time desc; 

2.把PDB启动到restricted模式，防止新连接进入
SQL> alter pluggable database CFPDB open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CFPDB                          READ WRITE NO
SQL> alter pluggable database CFPDB close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CFPDB                          MOUNTED
SQL> alter pluggable database CFPDB open restricted;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CFPDB                          READ WRITE YES
SQL> select name,open_mode,restricted from V$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE  RES
---------- ---
PDB$SEED
READ ONLY  NO

CFPDB
READ WRITE YES


3.进入PDB，修改名称
SQL> alter session set container=CFPDB;

Session altered.

SQL> alter pluggable database rename global_name to YZPDB;

Pluggable database altered.


4.重启PDB
SQL> alter pluggable database close immediate;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 YZPDB                          READ WRITE NO
         
SQL> set linesize 600
SQL> select name, open_mode, restricted from v$pdbs;

NAME                                                                                                                             OPEN_MODE  RES
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---
YZPDB                                                                                                                            READ WRITE NO
5.修改PDB数据文件目录

5.1  备份之前的pdb
cp -r CFPDB/ CFPDBBAK/

5.2在线修改
SQL> alter session set container=YZPDB;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/app/db/oradata/YZCDB/CFPDB/system01.dbf
/oracle/app/db/oradata/YZCDB/CFPDB/sysaux01.dbf
/oracle/app/db/oradata/YZCDB/CFPDB/undotbs01.dbf
/oracle/app/db/oradata/YZCDB/CFPDB/users01.dbf
/oracle/app/db/oradata/YZCDB/CFPDB/cfuser01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/oracle/app/db/oradata/YZCDB/CFPDB/temp01.dbf




SQL> alter database move datafile '/oracle/app/db/oradata/YZCDB/CFPDB/system01.dbf' to '/oracle/app/db/oradata/YZCDB/YZPDB/system01.dbf';

Database altered.

SQL> alter database move datafile '/oracle/app/db/oradata/YZCDB/CFPDB/sysaux01.dbf' to '/oracle/app/db/oradata/YZCDB/YZPDB/sysaux01.dbf';

Database altered.

SQL> alter database move datafile '/oracle/app/db/oradata/YZCDB/CFPDB/undotbs01.dbf' to '/oracle/app/db/oradata/YZCDB/YZPDB/undotbs01.dbf'
  2  ;

Database altered.

SQL> alter database move datafile '/oracle/app/db/oradata/YZCDB/CFPDB/users01.dbf' to '/oracle/app/db/oradata/YZCDB/YZPDB/users01.dbf';

Database altered.

SQL> alter database move datafile '/oracle/app/db/oradata/YZCDB/CFPDB/cfuser01.dbf' to '/oracle/app/db/oradata/YZCDB/YZPDB/cfuser01.dbf';

Database altered.

SQL> alter database move datafile '/oracle/app/db/oradata/YZCDB/CFPDB/temp01.dbf' to '/oracle/app/db/oradata/YZCDB/YZPDB/temp01.dbf';
alter database move datafile '/oracle/app/db/oradata/YZCDB/CFPDB/temp01.dbf' to '/oracle/app/db/oradata/YZCDB/YZPDB/temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/oracle/app/db/oradata/YZCDB/CFPDB/temp01.dbf" in the current container

4.8 重建temp临时表空间

1.删除之前的temp表空间
SQL> alter session set container=YZPDB;

Session altered.

SQL> alter database tempfile '/oracle/app/db/oradata/YZCDB/CFPDB/temp01.dbf' drop including datafiles;

Database altered.


2.创建新的temp表空间
SQL> alter session set container=YZPDB;
SQL> alter tablespace TEMP add  tempfile '/oracle/app/db/oradata/YZCDB/YZPDB/temp01.dbf' size 50M autoextend on;

Tablespace altered.

注意：如果安装时勾选了OMF管理方式，有两种方式move

1）常用方式 A 关闭数据库 B 在操作系统进行数据文件的mv操作 C 必须启动到mount状态 D 执行rman中常用的rename操作： alter database rename file ‘’ TO ‘’; E Open数据库

2）在表空间层进行move A Offline表空间 B 在操作系统进行数据文件的mv操作 C 执行表空间文件的rename操作： alter tablespace rename datafile ‘’ TO ‘’; D Online表空间