版本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       --默认此方式连接CDBConnected. 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.dbfSQL> host cp /picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/datafile/o1_mf_system_dzmh97yp_.dbf /picclife/app/oracle/oradata/mlxg/hukou/system.dbfSQL> 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/datafile/o1_mf_temp_dzmh97z3_.dbfSQL> host cp /picclife/app/oracle/oradata/mlxg/hukou/MLXG/5CEBF68574573767E053320AA8C05777/datafile/o1_mf_temp_dzmh97z3_.dbf /picclife/app/oracle/oradata/mlxg/hukou/temp.dbfSQL> 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 NOSQL> 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

相关内容推荐