BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

[原创]DB12cR2系列001_CDB/PDB操作

2017-07-17

[原创]DB12cR2系列001_CDB/PDB操作

容器数据库CDB和可插拔数据库PDB的一些常用操作

启动/停止CDB

跟之前11g启动停止实例操作一样

[oracle@databasevm ~]$ export ORACLE_SID=ORCLCDB;sqlplus / as sysdba                                              

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 19 22:32:34 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

																												  
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shu immediate;                   ###### 停止实例                                                                            
Database closed.                                                                                                  
Database dismounted.
ORACLE instance shut down.                                                                                        
SQL> startup                          ###### 启动实例和打开数据库                                                                            
ORACLE instance started.                                                                                          

Total System Global Area 1610612736 bytes
Fixed Size                  8793304 bytes
Variable Size             520094504 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.                                                                                                 
Database opened.                                                                                                  
SQL> 
SQL> select name,cdb,open_mode from v$database;         ###### 查看当前db是否cdb/pdb和状态                                                          

NAME      CDB OPEN_MODE
--------- --- --------------------
ORCLCDB   YES READ WRITE

SQL>     
SQL> show pdbs                                           ###### 查看当前pdb状态                                                          

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       READ WRITE NO
SQL>   

启动/停止PDB

参考如下:

SQL> show pdbs;                                                                                                   

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       MOUNTED
SQL>
SQL> alter pluggable database ORCLPDB1 open read only;                                                            
																												  
Pluggable database altered.

SQL> show pdbs;                                                                                                   

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       READ ONLY  NO
SQL>
SQL> alter pluggable database orclpdb1 close immediate;                                                           
																												  
Pluggable database altered.

SQL> alter pluggable database orclpdb1 open;                                                                      
																												  
Pluggable database altered.

SQL> show pdbs;                                                                                                   

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       READ WRITE NO
SQL>    

或者切换到pdb,跟11g一样启动停止

SQL> alter session set container=orclpdb1;                                                                        

Session altered.

SQL> show pdbs                                                                                                    

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 3 ORCLPDB1                       READ WRITE NO
SQL> shu immediate;                                                                                               
Pluggable Database closed.                                                                                        
SQL> show pdbs                                                                                                    

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 3 ORCLPDB1                       MOUNTED
SQL> conn / as sysdba                                                                                             
Connected.
SQL> show pdbs                                                                                                    

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       MOUNTED
SQL>                      

建议使用alter pluggable database XXXX open 类似的操作。

另外,如果很多PDB的时候,可以批量的操作:

全部PDB停止/启动

SQL> alter pluggable database all close;                                                                          
																												  
Pluggable database altered.

SQL> alter pluggable database all open;                                                                           
																												  
Pluggable database altered.

SQL> show pdbs                                                                                                    

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       READ WRITE NO
SQL>               

指定几个pdb一起操作

SQL> alter pluggable database ORCLPDB1,PDB$SEED open read only;                                                   
																												  
Pluggable database altered.

SQL> show pdbs                                                                                                    

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       READ ONLY  NO
SQL>         

** 只有一个种子PDB和ORCLPDB1,而种子PDB只有open read only状态

制定某个数据库之外其他库一起操作

SQL> alter pluggable database all close;                                                                          
																												  
Pluggable database altered.

SQL> show pdbs                                                                                                    

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       MOUNTED
SQL> alter pluggable database PDB$SEED  close;                                                                    
																												  
Pluggable database altered.

SQL> show pdbs                                                                                                    

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       MOUNTED
		 3 ORCLPDB1                       MOUNTED
SQL> alter pluggable database all except PDB$SEED open;                                                           
																												  
Pluggable database altered.

SQL> show pdbs;                                                                                                   

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       MOUNTED
		 3 ORCLPDB1                       READ WRITE NO
SQL>                

番外篇:自动启动pdb

启动全部pdb

SQL> alter pluggable database all open;

可以通过添加Trigger的形式来定制化startup时自动将PDB OPEN

使用SYS用户创建如下触发器即可: CREATE TRIGGER open_all_pdbs AFTER STARTUP ON DATABASE BEGIN EXECUTE IMMEDIATE ‘alter pluggable database all open’; END open_all_pdbs; /

即可实现

SQL> shu immediate;                                                                                               
Database closed.                                                                                                  
Database dismounted.
ORACLE instance shut down.                                                                                        
SQL> show pdbs            ### cdb实例停止后pdb也是关闭的                                                                                        
ERROR:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


SP2-1545: This feature requires Database availability.
SQL> startup                                                                                                      
ORACLE instance started.                                                                                          

Total System Global Area 1610612736 bytes
Fixed Size                  8793304 bytes
Variable Size             520094504 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7983104 bytes
Database mounted.                                                                                                 
Database opened.                                                                                                  
SQL> show pdbs;                                                                                                   

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 ORCLPDB1                       READ WRITE NO
SQL> 

~~ 2017/07/17 bigdata_lyn ~~


Comments