oracle 12c pdb日常维护

 

1.利用pdbseed模板创建pdb
SQL> create pluggable database ora12cpdb2 admin user pdbadmin2 identified by oracle
2 file_name_convert = ('/u01/app/oracle/oradata/ora12c/pdbseed', '/u01/app/oracle/oradata/ora12c/ora12cpdb2');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA12CPDB1 READ WRITE NO
4 ORA12CPDB2 MOUNTED
SQL> alter pluggable database ORA12CPDB2 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA12CPDB1 READ WRITE NO
4 ORA12CPDB2 READ WRITE NO

这里admin user后面的用户是pdb本地的用户，发现个奇怪的问题就是这样创建的账号采用plsql开发工具提示账号密码不对，无法登录(sqlplus倒是可以登录)，尝试如下修改密码才能登陆
alter user pdbadmin2 identified by oracle123;

该用户默认的角色为:PDB_DBA,该角色具有如下的权限：
create session
set container
create pluggable database

ASM的方式可以直接创建
create pluggable database pdb1 admin user updb1 identified by oracle;

 

2.使用CREATE_FILE_DEST参数创建pdb(不建议)

 

create pluggable database pdb04 admin user hxl identified by oracle roles=(dba)
CREATE_FILE_DEST='/u01/app/oracle/oradata/SLNNGK/pdb04';

 

该方式创建的的pdb目录并不是按照设想的那样创建,文件路径带有可读性差的guid
该方式创建的文件是在CREATE_FILE_DEST参数路径的基础上加上了 实例ID/GUID/datafile,而且文件名是以omf的方式命名.

 

 

2.启动pdb
alter pluggable database ORA12CPDB2 open;
或是如下启动索引的数据库
alter pluggable database all open;

3.切换容器
alter session set container=ORA12CPDB2;
--切换回根容器
alter session set container=cdb$root;

 

4.克隆pdbs
create pluggable database salespdbclone from salespdb
file_name_convert = ('/u01/app/oracle/oradata/ora12c/salespdb/', '/u01/app/oracle/oradata/ora12c/salespdbclone/')
path_prefix= '/u01/app/oracle/oradata/ora12c/salespdbclone';

alter pluggable database salespdbclone open;

pdbs=salespdbclone是从salespdb克隆过来的，相应的对象也一起克隆过来了.

SYS@cdbtest> select pdb_name,status from cdb_pdbs ;
如果status状态为NEED SYNC，要运行DBMS_PDB.SYNC_PDB将状态改变

5.pdb下的用户授权
alter session set container=ORA12CPDB1;
grant dba to hxl;

 

6.pdb重启动(需要关闭所有的连接，否则关闭一直hang住)
方法1:在根容器里执行
alter pluggable database ORA12CPDB1 close;
alter pluggable database ORA12CPDB1 open;

方法2:切换到相应的pdbs下执行
alter session set container=ORA12CPDB1;
alter pluggable database close;
alter pluggable database open;

 

7.创建公共用户
cdb下执行
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

create user c##hxl identified by oracle;
grant dba to c##hxl; ##这个授权只是针对cdb,要对pdb下的公共用户授权需要如下处理
alter session set container=ORA12CPDB1;
grant dba to c##hxl;

8.在root容器下的公共用户创建对象
sqlplus /nolog
connect C##GOLDENGATE/goldengate
create table tb_root(id number,name varchar2(32));

set linesize 1000;
column owner format a32;
column table_name format a32;
column tablespace_name format a32;
select owner,table_name,tablespace_name from dba_tables where owner='C##GOLDENGATE';
select table_name,tablespace_name from user_tables;

 

9.修改pdb参数

 

修改某个pdb参数
SQL> alter session set db_create_file_dest='' container=pdb04;
用于所有pdb
SQL> alter session set db_create_file_dest='' container=all;