版本12.2.0.1.0数据库,复制种子数据库快速创建租户数据库PDB

时间:2023-03-08 16:26:10
版本12.2.0.1.0数据库,复制种子数据库快速创建租户数据库PDB
实验测试:快速创建一个数据库PDB2;
实验环境:12.2.0.1.0版本数据库,dbca图形化安装,现有环境,CDB容器数据库ORCL,PDB可插拔数据库ABC
---查询CDB名称,状态

SQL>  select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;

NAME      Multitenant Option         OPEN_MODE                CON_ID

ORCL      Multitenant Option enabled READ WRITE                0

SQL> show pdbs       --查询PDB名称       ---只有CDB用户,可以查询当前容器下所有其他PDB名称状态,否则只能看自己的

2 PDB$SEED                 READ ONLY  NO

3 ABC                            READ WRITE NO

SQL> conn / as sysdba       --默认此方式连接CDB
Connected.
SQL> select con_id,file_name from cdb_data_files;    --CDB容器数据库,拥有所有权限,可以管理PDB租户数据库,应该还有种子数据库的数据文件,但是不可见
    CON_ID FILE_NAME
---------- ------------------------------------------------------------------
     1 /picclife/app/oracle/oradata/ORCL/datafile/o1_mf_users_dzjygrqc_.dbf
     1 /picclife/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_dzjygqms _.dbf
     1 /picclife/app/oracle/oradata/ORCL/datafile/o1_mf_system_dzjyclbn_.dbf
     1 /picclife/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_dzjyfy45_.dbf
     3 /picclife/app/oracle/oradata/ORCL/5CD8D682CC1E22D3E053280AA8C02164/datafile/o1_mf_system_dzjz2bxc_.dbf
     3 /picclife/app/oracle/oradata/ORCL/5CD8D682CC1E22D3E053280AA8C02164/datafile/o1_mf_sysaux_dzjz2by5_.dbf
     3 /picclife/app/oracle/oradata/ORCL/5CD8D682CC1E22D3E053280AA8C02164 /datafile/o1_mf_undotbs1_dzjz2by7_.dbf
     3 /picclife/app/oracle/oradata/ORCL/5CD8D682CC1E22D3E053280AA8C02164 /datafile/o1_mf_users_dzjz3x46_.dbf
[oracle@weixin oradata]$ mkdir -p /picclife/app/oracle/oradata/abc       --我们要快速建立一个数据库,打算将数据文件防止的目录,创建
---CDB,容器数据库SYS,执行:创建 可插拔 数据库 PDB2 管理员 pdd 密码 pdd  --格式      --数据文件存放目录 ‘xx’;
SQL> create pluggable database pdb2 admin user pdd identified by pdd roles=(connect) create_file_dest='/picclife/app/oracle/oradata/abc/';
 --查询:当前整个容器,所有的PDB,可插拔数据库
SQL> show pdbs;
    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 ABC                  READ WRITE NO
     5 PDB2               MOUNTED               ---复制后的数据库时Mount状态
SQL> alter pluggable database pdb2 open;        --类似单实例的alter database open;
SQL> select con_id ,file_name from cdb_data_files ; ---查询数据文件名称位置 复制过来的很乱
--------------------------------------------------------------------------------
    5/picclife/app/oracle/oradata/abc/ORCL/5CD97D00A8674014E053280AA8C0A8CA/datafile/o1_mf_system_dzk1tr0t_.dbf
     5/picclife/app/oracle/oradata/abc/ORCL/5CD97D00A8674014E053280AA8C0A8CA/datafile/o1_mf_sysaux_dzk1tr0w_.dbf
     5/picclife/app/oracle/oradata/abc/ORCL/5CD97D00A8674014E053280AA8C0A8CA/datafile/o1_mf_undotbs1_dzk1tr0x_.dbf
需要配置tnsnames.ora文件;  名称: ---12c连接需要配置tnsnames.ora文件,手工创建或者netca图形化创建
 PDB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PDB2)
    )
  )
修改表空间文件名称: 关闭pdb可插拔数据库;
alter pluggable database pdb2 close;
SQL> show pdbs
    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 ABC                  READ WRITE NO
     5 PDB2               MOUNTED
[oracle@weixin admin]$ sqlplus sys/oracle@pdb2 as sysdba   --连接到新建的复制数据库PDB2
SQL> show pdbs    --查看状态
    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     5 PDB2               MOUNTED
SQL> select name from v$datafile; --查询数据文件
NAME
--------------------------------------------------------------------------------
/picclife/app/oracle/oradata/abc/ORCL/5CD97D00A8674014E053280AA8C0A8CA/datafile/o1_mf_system_dzk1tr0t_.dbf
/picclife/app/oracle/oradata/abc/ORCL/5CD97D00A8674014E053280AA8C0A8CA/datafile/o1_mf_sysaux_dzk1tr0w_.dbf
/picclife/app/oracle/oradata/abc/ORCL/5CD97D00A8674014E053280AA8C0A8CA/datafile/o1_mf_undotbs1_dzk1tr0x_.dbf
--数据文件查询位置,名称。,CP过去,然后修改  :如下距离操作,重复执行,CP
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/datafile/o1_mf_system_dzmh97yp_.dbf
/picclife/app/oracle/oradata/mlxg/hukou/sysaux.dbf
/picclife/app/oracle/oradata/mlxg/hukou/undo.dbf
SQL> host cp /picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/datafile/o1_mf_system_dzmh97yp_.dbf /picclife/app/oracle/oradata/mlxg/hukou/system.dbf
SQL> alter database hukou rename file '/picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/datafile/o1_mf_system_dzmh97yp_.dbf' to '/picclife/app/oracle/oradata/mlxg/hukou/system.dbf';
Database altered.
--同理。临时数据文件修改
SQL>  select NAME from v$tempfile;
NAME
--------------------------------------------------------------------------------
/picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/da
tafile/o1_mf_temp_dzmh97z3_.dbf
SQL> host cp /picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/datafile/o1_mf_temp_dzmh97z3_.dbf /picclife/app/oracle/oradata/mlxg/hukou/temp.dbf
SQL> alter database hukou rename file '/picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/datafile/o1_mf_temp_dzmh97z3_.dbf' to '/picclife/app/oracle/oradata/mlxg/hukou/temp.dbf';
 日志文件,可插拔数据库等使用同一个日志文件
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/picclife/app/oracle/oradata/mlxg/redo03.log
/picclife/app/oracle/oradata/mlxg/redo02.log
/picclife/app/oracle/oradata/mlxg/redo01.log
SQL> alter pluggable database hukou open;       --启动可插拔数据库open;  此处,我使用的是另一个可插拔数据库hukou 接替pdb2测试,环境一样的,名字改了;
Pluggable database altered.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 HUKOU                          READ WRITE NO
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/picclife/app/oracle/oradata/mlxg/hukou/system.dbf
/picclife/app/oracle/oradata/mlxg/hukou/sysaux.dbf
/picclife/app/oracle/oradata/mlxg/hukou/undo.dbf
--12c控制文件与日志文件,都是使用大家公有的,至于undo,图形化DBCA建立数据库时,√选择,给予pdb可插拔数据库建立一个独立的undo,

SQL> select name from v$controlfile;

picclife/app/oracle/oradata/mlxg/control01.ctl

/picclife/app/oracle/fast_recovery_area/mlxg/control02.ctl