实验测试:快速创建一个数据库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