oracle12c cdb和pdb参数修改

12c的数据库启动参数文件只有一个,无论是cdb还是pdb使用这一个,但是其中这个参数文件其中只有cdb的数据库的参数.而没有pdb的参数.

   
   

    
    

     
     SQL> show parameter spfile
    
    
    
    

     
      
    
    
    
    

     
     NAME				     TYPE	 VALUE
    
    
    
    

     
     ------------------------------------ ----------- ------------------------------
    
    
    
    

     
     spfile				     string	 /u01/app/oracle/product/12.1.0
    
    
    
    

     
     						 /db_1/dbs/spfilesuq1.ora
    
    
   
   

查看参数文件的内容:

   
   

    
    

     
     [oracle@12c1 ~]$ cat pfile.ora 
    
    
    
    

     
      
    
    
    
    

     
     suq1.__data_transfer_cache_size=0
    
    
    
    

     
     suq1.__db_cache_size=444596224
    
    
    
    

     
     suq1.__java_pool_size=4194304
    
    
    
    

     
     suq1.__large_pool_size=8388608
    
    
    
    

     
     suq1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    
    
    
    

     
     suq1.__pga_aggregate_target=373293056
    
    
    
    

     
     suq1.__sga_target=700448768
    
    
    
    

     
     suq1.__shared_io_pool_size=25165824
    
    
    
    

     
     suq1.__shared_pool_size=209715200
    
    
    
    

     
     suq1.__streams_pool_size=0
    
    
    
    

     
     *.audit_file_dest='/u01/app/oracle/admin/suq1/adump'
    
    
    
    

     
     *.audit_trail='db'
    
    
    
    

     
     *.compatible='12.1.0.2.0'
    
    
    
    

     
     *.control_files='/u01/oradata/suq1/control01.ctl','/u01/flasharea/suq1/control02.ctl'
    
    
    
    

     
     *.db_block_size=8192
    
    
    
    

     
     *.db_domain=''
    
    
    
    

     
     *.db_name='suq1'
    
    
    
    

     
     *.db_recovery_file_dest='/u01/flasharea'
    
    
    
    

     
     *.db_recovery_file_dest_size=4815m
    
    
    
    

     
     *.diagnostic_dest='/u01/app/oracle'
    
    
    
    

     
     *.dispatchers='(PROTOCOL=TCP) (SERVICE=suq1XDB)'
    
    
    
    

     
     *.enable_pluggable_database=true
    
    
    
    

     
     *.memory_target=1024m
    
    
    
    

     
     *.open_cursors=600
    
    
    
    

     
     *.processes=300
    
    
    
    

     
     *.remote_login_passwordfile='EXCLUSIVE'
    
    
    
    

     
     *.undo_tablespace='UNDOTBS1'
    
    
   
   

而pdb的参数分为两部分组成:

1.默认的pdb的参数由cdb参数指定

2.特别的如果手工指定了某pdb的参数,那么此pdb的参数将覆盖cdb的参数.不再参考cdb的参数值.而且此pdb的参数是存放在数据字典中.

下面我们看一个实例:

当前cdb的参数open_cursor为600,即上面参数文件中指定的:

   
   

    
    

     
     SQL> conn / as sysdba
    
    
    
    

     
     Connected.
    
    
    
    

     
     SQL> show con_name
    
    
    
    

     
      
    
    
    
    

     
     CON_NAME
    
    
    
    

     
     ------------------------------
    
    
    
    

     
     CDB$ROOT
    
    
    
    

     
     SQL> show parameter open_cursor
    
    
    
    

     
      
    
    
    
    

     
     NAME				     TYPE	 VALUE
    
    
    
    

     
     ------------------------------------ ----------- ------------------------------
    
    
    
    

     
     open_cursors			     integer	 600
    
    
    
    

     
     SQL> 
    
    
   
   

而此时pdb2的参数也为600:

   
   

    
    

     
     SQL> alter session set container=pdb2;
    
    
    
    

     
      
    
    
    
    

     
     Session altered.
    
    
    
    

     
      
    
    
    
    

     
     SQL> show con_name
    
    
    
    

     
      
    
    
    
    

     
     CON_NAME
    
    
    
    

     
     ------------------------------
    
    
    
    

     
     PDB2
    
    
    
    

     
     SQL> show parameter open_cursor;
    
    
    
    

     
      
    
    
    
    

     
     NAME				     TYPE	 VALUE
    
    
    
    

     
     ------------------------------------ ----------- ------------------------------
    
    
    
    

     
     open_cursors			     integer	 600
    
    
   
   

此时我们修改cdb的参数:

    
    

     
     

      
      SQL> conn / as sysdba            
     
     
     
     

      
      Connected.
     
     
     
     

      
      SQL> show con_name
     
     
     
     

      
       
     
     
     
     

      
      CON_NAME
     
     
     
     

      
      ------------------------------
     
     
     
     

      
      CDB$ROOT
     
     
     
     

      
       
     
     
     
     

      
      SQL> alter system set open_cursors=500;
     
     
     
     

      
       
     
     
     
     

      
      System altered.
     
     
     
     

      
       
     
     
     
     

      
      SQL> show parameter open_cursors
     
     
     
     

      
       
     
     
     
     

      
      NAME				     TYPE	 VALUE
     
     
     
     

      
      ------------------------------------ ----------- ------------------------------
     
     
     
     

      
      open_cursors			     integer	 500
     
     
     
     

      
      SQL> alter session set container=pdb2;
     
     
     
     

      
       
     
     
     
     

      
      Session altered.
     
     
     
     

      
       
     
     
     
     

      
      SQL> show con_name
     
     
     
     

      
       
     
     
     
     

      
      CON_NAME
     
     
     
     

      
      ------------------------------
     
     
     
     

      
      PDB2
     
     
     
     

      
      SQL> show parameter open_cursor;
     
     
     
     

      
       
     
     
     
     

      
      NAME				     TYPE	 VALUE
     
     
     
     

      
      ------------------------------------ ----------- ------------------------------
     
     
     
     

      
      open_cursors			     integer	 500
     
     
    
    

我们发现pdb的参数也跟着修改了,说明pdb的参数是有cdb参数控制的.

继续,我们手工修改pdb的参数:

  
  

   
   

    
    SQL> show con_name
   
   
   
   

    
     
   
   
   
   

    
    CON_NAME
   
   
   
   

    
    ------------------------------
   
   
   
   

    
    PDB2
   
   
   
   

    
    SQL> alter system set open_cursors=1000;
   
   
   
   

    
     
   
   
   
   

    
    System altered.
   
   
   
   

    
     
   
   
   
   

    
    SQL> conn / as sysdba
   
   
   
   

    
    Connected.
   
   
   
   

    
    SQL> show parameter open_cursors
   
   
   
   

    
     
   
   
   
   

    
    NAME				     TYPE	 VALUE
   
   
   
   

    
    ------------------------------------ ----------- ------------------------------
   
   
   
   

    
    open_cursors			     integer	 500
   
   
   
   

    
    SQL> alter system set open_cursors=800;
   
   
   
   

    
     
   
   
   
   

    
    System altered.
   
   
   
   

    
     
   
   
   
   

    
    SQL> show con_name
   
   
   
   

    
     
   
   
   
   

    
    CON_NAME
   
   
   
   

    
    ------------------------------
   
   
   
   

    
    CDB$ROOT
   
   
   
   

    
    SQL> alter session set container=pdb2;
   
   
   
   

    
     
   
   
   
   

    
    Session altered.
   
   
   
   

    
     
   
   
   
   

    
    SQL> show parameter open_cursors
   
   
   
   

    
     
   
   
   
   

    
    NAME				     TYPE	 VALUE
   
   
   
   

    
    ------------------------------------ ----------- ------------------------------
   
   
   
   

    
    open_cursors			     integer	 1000
   
   
  
  

我们发现手工修改pdb的参数之后,那么此参数就不受cdb参数控制了.再修改cdb的参数也无法修改pdb的参数值.

可以通过查看数据字典pdb_spfile$来查看pdb指定的参数:

   
   

    
    

     
     SQL> select a.name,a.dbid,b.pdb_uid,b.name,b.value$ from v$pdbs a ,pdb_spfile$ b where a.dbid=b.pdb_uid and a.name='PDB2'
    
    
    
    

     
     SQL> /
    
    
    
    

     
      
    
    
    
    

     
     NAME				     DBID    PDB_UID NAME									      VALUE$
    
    
    
    

     
     ------------------------------ ---------- ---------- -------------------------------------------------------------------------------- ------------------------------
    
    
    
    

     
     PDB2			       2158045800 2158045800 db_securefile								      'PREFERRED'
    
    
    
    

     
     PDB2			       2158045800 2158045800 open_cursors								      1000