ORACLE 12C PDB 維護基礎介紹
CDB和PDB是ORACLE 12C一個很亮的新特性,由於他們的引入導致傳統的ORACLE資料庫管理理念不少發生了改變,這裡列舉了部分最基本的cdb和pdb管理方式
cdb和pdb關係圖
ORACLE 12C版本
|
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
|
啟動關閉pdb
|
SQL> startup
ORACLE instance started.
Total System Global Area 597098496 bytes
Fixed Size 2291072 bytes
Variable Size 272632448 bytes
Database Buffers 314572800 bytes
Redo Buffers 7602176 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database pdb1 open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database pdb1 close;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 READ WRITE
SQL> alter PLUGGABLE database all close;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> startup
Pluggable Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
3 3313918585 PDB1 READ WRITE
|
pdb的管理可以在cdb中進行也可以在pdb中進行,如果是cdb中進行,需要PLUGGABLE關鍵字,如果是pdb中直接和普通資料庫一樣
登入pdb
|
[oracle@xifenfei ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 12-MAY-2013 08:07:02
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 11-MAY-2013 18:30:54
Uptime 0 days 13 hr. 36 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/product/12.1/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xifenfei)(PORT=5500))
(Security=(my_wallet_directory=/u01/oracle/12.1/db_1/admin/cdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "cdb" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@xifenfei ~]$ tnsping pdb1
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 12-MAY-2013 08:07:09
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))
OK (20 msec)
[oracle@xifenfei ~]$ sqlplus sys/xifenfei@pdb1 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun May 12 08:08:02 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> show con_name;
CON_NAME
------------------------------
PDB1
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun May 12 08:09:14 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB1
|
pdb可以通過alter session container進入也可以直接通過tns方式登入
建立使用者
|
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 MOUNTED
SQL> create user xff identified by xifenfei;
create user xff identified by xifenfei
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> !oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause: An attempt was made to create a common user or role with a name
// that wass not valid for common users or roles. In addition to
// the usual rules for user and role names, common user and role
// names must start with C## or c## and consist only of ASCII
// characters.
// *Action: Specify a valid common user or role name.
//
SQL> create user c##xff identified by xifenfei;
User created.
SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS WHERE USERNAME='C##XFF';
USERNAME CON_ID USER_ID
---------- ---------- ----------
C##XFF 1 103
C##XFF 3 104
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> create user xff identified by xifenfei;
User created.
SQL> create user c##abc identified by xifenfei;
create user c##abc identified by xifenfei
*
ERROR at line 1:
ORA-65094: invalid local user or role name
|
建立使用者預設的是container=all,在cdb中只能建立全域性使用者(c##開頭),會在cdb和所有的pdb中建立該使用者(但是pdb中的全域性使用者需要另外授權才能夠在pdb中訪問)。在pdb中只能建立的使用者為本地使用者
使用者授權
|
SQL> grant connect to c##xff;
Grant succeeded.
SQL> select GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND GRANTEE='C##XFF';
GRANTEE CON_ID
------------------------------ ----------
C##XFF 1
SQL> grant resource to c##xff container=all;
Grant succeeded.
SQL> select GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='RESOURCE' AND GRANTEE='C##XFF';
GRANTEE CON_ID
------------------------------ ----------
C##XFF 1
C##XFF 3
|
使用者授權預設情況下是隻會給當前container,在cdb中也可以指定container=all,對所有open的pdb且存在該使用者都進行授權
修改引數
|
SQL> alter system set open_cursors=500 container=all;
System altered.
SQL> conn sys/xifenfei@pdb1 as sysdba
Connected.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 500
SQL> alter system set open_cursors=100;
alter system set open_cursors=100
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> alter database open;
Database altered.
SQL> alter system set open_cursors=100;
System altered.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 100
SQL> conn / as sysdba
Connected.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 500
|
這裡可以看到在cdb中修改,pdb會繼承進去;如果在pdb中修改會覆蓋pdb從cdb中繼承的引數含義
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2135393/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【PDB】Oracle pdb維護常用sql命令OracleSQL
- 1、Oracle 基礎介紹Oracle
- Jumpserver基礎運維-01介紹Server運維
- ORACLE 12C Cloud Control（二）介紹OracleCloud
- oracle 12c rman備份pdbOracle
- Oracle 12c系列(五)｜PDB RefreshOracle
- Oracle 12c系列(六)｜Relocate a PDBOracle
- Oracle 12c系列(二)｜PDB的建立Oracle
- ORACLE 12C pdb受限解決思路Oracle
- oracle 12c PDB隨CDB啟動和連結PDB的方式Oracle
- ORACLE 12C RAC 生產環境搭建介紹Oracle
- Pandas基礎介紹
- Elasticsearch 基礎介紹Elasticsearch
- Oracle 12c RMAN Cross-Platform Transport of a Closed PDBOracleROSPlatform
- JDBC的基礎介紹JDBC
- ActiveMq的基礎介紹MQ
- MySQL 教程基礎介紹MySql
- Oracle 12c系列(七) | Non-CDB轉換為PDBOracle
- Oracle 12c RMAN Performing Cross-Platform Transport of a PDB Using InconsistentOracleROSPlatform
- OSPF介紹及基礎配置
- RocketMQ系列1：基礎介紹MQ
- Django基礎教程之介紹Django
- 【ASK_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（一）什麼是CDB與PDB？Oracle
- 12c pdb基本操作
- Oracle 12C 中CDB和PDB的引數檔案管理Oracle
- 【BAK_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（三）CDB與PDB的備份方式Oracle
- 【RECO_ORACLE】Oracle 12c之CDB與PDB的備份與恢復（四）PDB的幾種恢復方式Oracle
- Android RxJava：基礎介紹與使用AndroidRxJava
- 資料倉儲基礎介紹
- 02-ASP.Net(基礎介紹)ASP.NET
- 【ES6基礎】const介紹
- Python介紹和基礎運用Python
- Linux shell 指令碼基礎介紹Linux指令碼
- oracle 12c中CDB和PDB的備份還原實驗Oracle
- Oracle OGG日常維護Oracle
- 【Oracle】Oracle logminer功能介紹Oracle
- 【12c cdb pdb】實驗
- ORACLE OWI介紹Oracle
- Oracle ADR介紹Oracle