设为首页收藏本站

Botang唐波's Oracle Station

查看: 202|回复: 0

课程第46/47次(2018-05-20星期日上下午)

[复制链接]

708

主题

1070

帖子

7759

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7759
发表于 2018-5-20 10:43:52 | 显示全部楼层 |阅读模式
CDB2:
  1. select name from v$services;

  2. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
  3. from v_$tablespace t , v_$datafile d
  4. where t.TS#=d.TS# and t.CON_ID=d.CON_ID

  5. order by 1;

  6. --------------
  7. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
  8. from v_$tablespace t , v_$tempfile d
  9. where t.TS#=d.TS# and t.CON_ID=d.CON_ID

  10. order by 1;

  11. ---------

  12. select  con_id, name , open_mode from v$pdbs;

  13. drop pluggable database pdb2_1 including datafiles;

  14. alter pluggable database pdb2_1 close immediate;

  15. ------------------

  16. create pluggable database pdb2_2 from pdb2_1
  17. file_name_convert=('/u01/app/oracle/oradata/cdb2/pdb2_1',
  18.     '/u01/app/oracle/oradata/cdb2/pdb2_2');
  19.    
  20.   ---------------------
  21.   select  con_id, name ,  open_mode from v$pdbs;
  22.   
  23.   alter pluggable database pdb2_2 open;
  24.   
  25.   -----------------------
  26. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
  27. from v_$tablespace t , v_$datafile d
  28. where t.TS#=d.TS# and t.CON_ID=d.CON_ID
  29. order by 1;
  30. -----------------------
  31. alter pluggable database pdb2_2 close immediate;

  32. drop pluggable database  pdb2_2  including datafiles;
  33. -----
  34. alter system set db_create_file_dest='/u01/app/oracle/oradata/cdb2/pdb2_2';

  35. create pluggable database pdb2_2 from pdb2_1;
  36. -----
  37. select  * from cdb_pdbs;


复制代码

PDB2_1:
  1. create user hr identified by oracle_4U  ;

  2. grant connect , resource to hr ;

  3. select  * from cdb_sys_privs  sp where sp.grantee='HR';

  4. select  * from cdb_tab_privs  tp where tp.grantee='HR';

  5. select  * from cdb_role_privs  rp where rp.grantee='HR';

  6. select  * from role_sys_privs  rsp where rsp.role='RESOURCE';

  7. create tablespace users
  8. datafile '/u01/app/oracle/oradata/cdb2/pdb2_1/users01.dbf' size 20M ;

  9. alter user hr default tablespace users quota unlimited on  users;

  10. select  * from cdb_ts_quotas  tq;

  11. select open_mode from v$database;
复制代码

PDB2_2:
  1. select  open_mode from v$database;

  2. alter database open;

  3. select  * from hr.t06303_a;

  4. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
  5. from v_$tablespace t , v_$datafile d
  6. where t.TS#=d.TS# and t.CON_ID=d.CON_ID
  7. order by 1;

  8. --------------
  9. select t.CON_ID, t.NAME, d.NAME  , d.STATUS
  10. from v_$tablespace t , v_$tempfile d
  11. where t.TS#=d.TS# and t.CON_ID=d.CON_ID

  12. order by 1;
复制代码
PDB_ORCL2(与1Z0-063书上不同):

  1. select  con_id, name , open_mode from v$pdbs;

  2. create pluggable database pdb_orcl2  using
  3. '/home/oracle/orcl2.xml'
  4. file_name_convert=( '+DATA/ORCL2/DATAFILE/system.266.976031403',
  5.                                  '/u01/app/oracle/oradata/cdb2/pdb_orcl2/system01.dbf',
  6.                                 '+DATA/ORCL2/DATAFILE/sysaux.269.976031405',
  7.                                 '/u01/app/oracle/oradata/cdb2/pdb_orcl2/sysaux01.dbf',
  8.                                 '+DATA/ORCL2/DATAFILE/users.268.976031411',
  9.                                 '/u01/app/oracle/oradata/cdb2/pdb_orcl2/users01.dbf',
  10.                                 '+DATA/ORCL2/DATAFILE/example.267.976031415',
  11.                                 '/u01/app/oracle/oradata/cdb2/pdb_orcl2/example01.dbf',
  12.                                 '+DATA/ORCL2/TEMPFILE/temp.259.976031881',
  13.                                  '/u01/app/oracle/oradata/cdb2/pdb_orcl2/temp01.dbf'                                
  14. );

  15. select  con_id, name , open_mode from v$pdbs;
复制代码

CDB1拔出PDB1_1:

  1. select name, open_mode from v$pdbs;

  2. alter pluggable database pdb1_1 unplug into '/home/oracle/pdb1_1.xml';

  3. alter pluggable database pdb1_1 close ;

  4. alter pluggable database pdb1_1 unplug into '/home/oracle/pdb1_1.xml';

  5. select  * from cdb_pdbs;

  6. drop pluggable database pdb1_1 keep datafiles;


复制代码
CDB2插入PDB1_1:
  1. DECLARE
  2.   compat BOOLEAN := FALSE;
  3. BEGIN
  4. compat := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  5. pdb_descr_file =>'/home/oracle/pdb1_1.xml',
  6. pdb_name => 'pdb1_1');
  7. if compat
  8.   then
  9.   DBMS_OUTPUT.PUT_LINE('Is pluggable compatible? YES');
  10. else
  11.    DBMS_OUTPUT.PUT_LINE('Is pluggable compatible? NO');
  12.   end if;
  13. end;

  14. select  * from pdb_plug_in_violations;

  15. create pluggable database pdb1_1 using  '/home/oracle/pdb1_1.xml' nocopy;

  16. select  con_id, name ,  open_mode from v$pdbs;

  17. alter pluggable database  pdb1_1  open;
复制代码
带着插件库删除根容器:
  1. select name, open_mode from v$pdbs;

  2. create pluggable database dog admin  user pdbadmin
  3. identified by oracle_4U roles=(connect);

  4. alter pluggable database dog open;
复制代码
启动和关闭插件数据库:

alter pluggable database xxx close和alter pluggable database xxx  close immediate的区别:前者有会话连接就关闭不掉。

在close的插件数据库上还能够用SYS来连接。

插件数据库的信息只有在mount的时候才能看(插件的物理结构在控制文件里),数据字典在各自的SYSTEM/SYSAUX表空间里。

  1. alter pluggable database pdb2_1 close immediate;

  2. select con_id, name , open_mode from v$pdbs;

  3. alter pluggable database pdb2_1 open;

  4. alter pluggable database pdb2_1 close;

  5. select  * from dba_triggers;

  6. create trigger open_all_pdbs
  7.   after startup on database
  8.   begin
  9.    execute immediate 'alter pluggable database all open';
  10.   end;
  11.   
  12.   select  * from dba_source s where s.OWNER='SYS' and s.NAME='OPEN_ALL_PDBS';
复制代码

关于插件数据库restricted session:
  1. SQL> conn sys/oracle_4U@pdb2_1 as sysdba
  2. Connected.
  3. SQL> alter pluggable database  rename global_name to pdb2;
  4. alter pluggable database  rename global_name to pdb2
  5.                                                 *
  6. ERROR at line 1:
  7. ORA-65045: pluggable database not in a restricted mode


  8. SQL> shutdown immediate
  9. Pluggable Database closed.
  10. SQL> alter pluggable database open restricted;

  11. Pluggable database altered.

  12. SQL> alter pluggable database  rename global_name to pdb2;

  13. Pluggable database altered.

  14. SQL> select con_id, name , open_mode from v$pdbs;

  15.     CON_ID NAME                           OPEN_MODE
  16. ---------- ------------------------------ ----------
  17.          3 PDB2                           READ WRITE

  18. SQL> alter system disable restricted session ;

  19. System altered.

  20. SQL>
复制代码

有些参数在插件上能改,有些不能改:
  1. select ISPDB_MODIFIABLE from v$parameter
  2. where name='optimizer_use_sql_plan_baselines';


  3. select ISPDB_MODIFIABLE from v$parameter
  4. where name='control_files';
复制代码
看同一个参数的不同的值,请查v$system_parameter;

每一个插件数据库的个性化参数应该各自的数据字典有关系:
  1. SQL> startup nomount
  2. ORACLE instance started.

  3. Total System Global Area 4294967296 bytes
  4. Fixed Size                    2932632 bytes
  5. Variable Size                 2583691368 bytes
  6. Database Buffers         1694498816 bytes
  7. Redo Buffers                   13844480 bytes
  8. select CON_ID, VALUE from
  9.   2  V$SYSTEM_PARAMETER
  10.   3  where name ='optimizer_use_sql_plan_baselines';

  11.     CON_ID
  12. ----------
  13. VALUE
  14. --------------------------------------------------------------------------------
  15.          0
  16. TRUE


  17. SQL> alter database mount;

  18. Database altered.

  19. SQL> set linesize 10000
  20. SQL> select CON_ID, VALUE from
  21.   2  V$SYSTEM_PARAMETER
  22.   3  where name ='optimizer_use_sql_plan_baselines';

  23.     CON_ID VALUE
  24. ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  25.          0 TRUE

  26. SQL> alter database open ;

  27. Database altered.

  28. SQL> select CON_ID, VALUE from
  29.   2  V$SYSTEM_PARAMETER
  30.   3  where name ='optimizer_use_sql_plan_baselines';

  31.     CON_ID VALUE
  32. ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  33.          0 TRUE
  34.          3 FALSE

  35. SQL>
复制代码

PDB_ORC2(从外面插进来的普通12c的数据库)没有继承到的根容器的通用用户:
Screenshot.png


根容器上的新的通用用户的继承性关系:

a.png


新的创建用户的语法(container=ALL):
  1. create user c##u identified by x;

  2. select  c.CON_ID, c.USERNAME,
  3.     c.COMMON,
  4.     c.DEFAULT_TABLESPACE,
  5.    c.TEMPORARY_TABLESPACE
  6. from cdb_users c
  7.   where c.USERNAME='C##U'
  8.   order by 1;
  9.   
  10.   create user c##_user identified by x container=all;
  11.   
  12.   select  c.CON_ID, c.USERNAME,
  13.     c.COMMON,
  14.     c.DEFAULT_TABLESPACE,
  15.    c.TEMPORARY_TABLESPACE
  16. from cdb_users c
  17.   where c.USERNAME='C##_USER'
  18.   order by 1;
复制代码




回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-8-20 02:54 , Processed in 0.120385 second(s), 27 queries .

快速回复 返回顶部 返回列表