使用CREATE_FILE_DEST创建pdb遇到的文件路径问题

标签：DEST CREATE pdb04 oradata FILE oracle dbf app u01

环境
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 --

标签：DEST,CREATE,pdb04,oradata,FILE,oracle,dbf,app,u01
From： https://www.cnblogs.com/hxlasky/p/17105668.html

使用CREATE_FILE_DEST创建pdb遇到的文件路径问题

相关文章

赞助商

阅读排行