使用CREATE_FILE_DEST创建pdb遇到的文件路径问题
环境
OS:Centos 7
DB:19.3.0.0 一主一从 dataguard
############################创建pdb################################
1.主库上查看db_create_file_dest参数
SQL> show parameters db_create_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
该参数为空,没有进行设置
2.通过CREATE_FILE_DEST参数创建pdb
先创建文件目录
su - oracle
mkdir -p /u01/app/oracle/oradata/SLNNGK/pdb04
创建pdb
create pluggable database pdb04 admin user hxl identified by oracle roles=(dba)
CREATE_FILE_DEST='/u01/app/oracle/oradata/SLNNGK/pdb04';
SQL> alter pluggable database pdb04 open;
3.文件路径查看
这种方式创建的pdb文件如下,并我不是自己设想的路径,
该方式创建的文件是在CREATE_FILE_DEST参数路径的基础上加上了 实例ID/GUID/datafile,而且文件名是以omf的方式命名
alter session set container=pdb04;
Set linesize 1000;
Column file_name format a128;
Column file_id format 99;
Column tablespace_name format a10;
Select file_name,file_id,tablespace_name From dba_data_files;
SQL> Select file_name,file_id,tablespace_name From dba_data_files;
FILE_NAME FILE_ID TABLESPACE
-------------------------------------------------------------------------------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/SLNNGK/pdb04/SLNNGK/F42702875A22084EE05502296C265E07/datafile/o1_mf_system_ky617997_.dbf 30 SYSTEM
/u01/app/oracle/oradata/SLNNGK/pdb04/SLNNGK/F42702875A22084EE05502296C265E07/datafile/o1_mf_sysaux_ky617997_.dbf 31 SYSAUX
/u01/app/oracle/oradata/SLNNGK/pdb04/SLNNGK/F42702875A22084EE05502296C265E07/datafile/o1_mf_undotbs1_ky617999_.dbf 32 UNDOTBS1
4.从库上打开pdb
首先创建相应路径
su - orace
mkdir -p /u01/app/oracle/oradata/slavea/pdb04/slavea
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ ONLY NO
4 PDB01 READ ONLY NO
5 PDB02 READ ONLY NO
6 PDB03 READ ONLY NO
7 PDB04 MOUNTED
SQL> alter pluggable database pdb04 open;
Pluggable database altered.
5.从库上查看数据文件
SQL> alter session set container=pdb04;
SQL>Set linesize 1000;
SQL> Column file_name format a128;
SQL> Column file_id format 99;
SQL> Column tablespace_name format a10;
SQL> Select file_name,file_id,tablespace_name From dba_data_files;
SQL> Select file_name,file_id,tablespace_name From dba_data_files;
FILE_NAME FILE_ID TABLESPACE
-------------------------------------------------------------------------------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_system_ky617997_.dbf 30 SYSTEM
/u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_sysaux_ky617997_.dbf 31 SYSAUX
/u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_undotbs1_ky617999_.dbf 32 UNDOTBS1
####################################修改文件路径###########################
12C之后可以在线修改文件路径
1.先修改主库文件
迁移数据文件
connect / as sysdba
alter session set container=pdb04;
alter database move datafile 30 to '/u01/app/oracle/oradata/SLNNGK/pdb04/system01.dbf';
alter database move datafile 31 to '/u01/app/oracle/oradata/SLNNGK/pdb04/sysaux01.dbf';
alter database move datafile 32 to '/u01/app/oracle/oradata/SLNNGK/pdb04/undotbs01.dbf';
发现原来目录的文件以及删除
[oracle@19c pdb04]$ ls -al /u01/app/oracle/oradata/SLNNGK/pdb04/SLNNGK/F42702875A22084EE05502296C265E07/datafile
total 56
drwxr-x--- 2 oracle oinstall 38 Feb 7 21:57 .
drwxr-x--- 3 oracle oinstall 22 Feb 7 21:11 ..
-rw-r----- 1 oracle oinstall 37756928 Feb 7 21:43 o1_mf_temp_ky61799b_.dbf
迁移临时文件
SQL> alter session set container=cdb$root;
Session altered.
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/SLNNGK/temp01.dbf
2 /u01/app/oracle/oradata/SLNNGK/pdbseed/temp012023-02-06_04-46-52-866-AM.dbf
3 /u01/app/oracle/oradata/SLNNGK/pdb/temp01.dbf
4 /u01/app/oracle/oradata/SLNNGK/pdb01/temp01.dbf
5 /u01/app/oracle/oradata/SLNNGK/pdb02/temp01.dbf
6 /u01/app/oracle/oradata/SLNNGK/pdb03/temp012023-02-06_04-46-52-866-AM.dbf
7 /u01/app/oracle/oradata/SLNNGK/pdb04/SLNNGK/F42702875A22084EE05502296C265E07/datafile/o1_mf_temp_ky61799b_.dbf
7 rows selected.
SQL> alter database move tempfile 7 to '/u01/app/oracle/oradata/SLNNGK/pdb04/temp01.dbf';
alter database move tempfile 7 to '/u01/app/oracle/oradata/SLNNGK/pdb04/temp01.dbf'
*
ERROR at line 1:
ORA-00905: missing keyword
不管用,下面尝试为临时表空间添加数据文件,然后删除旧的数据文件
查看临时表空间
SQL> alter session set container=pdb04;
SQL> select file_name,file_id,tablespace_name,MAXBYTES,AUTOEXTENSIBLE from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE MAXBYTES AUT
-------------------------------------------------------------------------------------------------------------------------------- ------- ---------- ---------------------------------------- ---
/u01/app/oracle/oradata/SLNNGK/pdb04/SLNNGK/F42702875A22084EE05502296C265E07/datafile/o1_mf_temp_ky61799b_.dbf 7 TEMP 34359721984 YES
添加一个正确路径的临时文件
alter tablespace temp add tempfile '/u01/app/oracle/oradata/SLNNGK/pdb04/temp01.dbf' size 50m autoextend on next 5M maxsize 34359721984;
删除路径不正确的临时文件
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/SLNNGK/pdb04/SLNNGK/F42702875A22084EE05502296C265E07/datafile/o1_mf_temp_ky61799b_.dbf';
这样原来目录的文件都会自动删除
这个时候可以将不正确的目录删除掉
[oracle@19c datafile]$ cd /u01/app/oracle/oradata/SLNNGK/pdb04
[oracle@19c pdb04]$ ls
SLNNGK sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf
[oracle@19c pdb04]$ rm -rf SLNNGK/
2.修改从库文件路径
备库数据文件改名操作过程
设置如下参数为手工模式
alter system set standby_file_management='manual' scope=both;
重启数据库到mount模式
shutdown immediate
startup mount
迁移数据文件
mv /u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_system_ky617997_.dbf /u01/app/oracle/oradata/slavea/pdb04/system01.dbf
mv /u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_sysaux_ky617997_.dbf /u01/app/oracle/oradata/slavea/pdb04/sysaux01.dbf
mv /u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_undotbs1_ky617999_.dbf /u01/app/oracle/oradata/slavea/pdb04/undotbs01.dbf
修改数据字典信息
su - oracle
alter database rename file '/u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_system_ky617997_.dbf' to '/u01/app/oracle/oradata/slavea/pdb04/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_sysaux_ky617997_.dbf' to '/u01/app/oracle/oradata/slavea/pdb04/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/slavea/pdb04/slavea/F42702875A22084EE05502296C265E07/datafile/o1_mf_undotbs1_ky617999_.dbf' to '/u01/app/oracle/oradata/slavea/pdb04/undotbs01.dbf';
打开数据库并恢复参数standby_file_management为自动模式，同时应用日志
alter database open;
alter system set standby_file_management='AUTO' scope=both;
alter database recover managed standby database using current logfile disconnect from session;
-- The End --
