Oracle12c日常维护

1.启动CDB和PDB数据库

创新互联成都网站建设定制网站制作，是成都网站营销公司,为社区文化墙提供网站建设服务,有成熟的网站定制合作流程，提供网站定制设计服务：原型图制作、网站创意设计、前端HTML5制作、后台程序开发等。成都网站维护热线：18982081108

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

CON_ID DBID NAME OPEN_MODE

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

2 1226396669 PDB$SEED READ ONLY

3 411454583 DB12CCD 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 1226396669 PDB$SEED READ ONLY

3 411454583 DB12CCD READ WRITE

2.创建PDB数据库

SQL> select name,cdb from v$database;

NAME CDB

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

DB12C YES

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

PDB_ID PDB_NAME DBID STATUS CREATION_SCN

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

3 DB12CCD 411454583 NORMAL 1744330

2 PDB$SEED 1226396669 NORMAL 1594399

SQL> create pluggable database test admin user test identified by test file_name_convert=('/u01/app/oracle/oradata/cdb/pdbseed/','/u01/app/oracle/oradata/cdb/test/');

Pluggable database created.

create pluggable database test1 admin user test1 identified by test1 file_name_convert=('/u01/app/oracle/oradata/cdb/test/','/u01/app/oracle/oradata/cdb/test1/');

3.建立连接

--进入cdb

sqlplus / as sysdba

sqlplus sys/sys@192.168.10.32:1521/cdb as sysdba

--进入pdb

3.1)通过tns方式用创建的admin user登录

[oracle@ora12cA ~]$ sqlplus sys/oracle@192.168.10.32:1521/test as sysdba

SQL> show con_name

CON_NAME

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

TEST

--查看admin user拥有的权限

SQL> select * from session_privs;

PRIVILEGE

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

SET CONTAINER

CREATE PLUGGABLE DATABASE

CREATE SESSION

3.2)通过set container来连接pdb

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB MOUNTED

4 TEST READ WRITE NO

SQL> alter session set container=test;

Session altered.

SQL> show con_name

CON_NAME

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

TEST

4.打开和关闭pdb

SQL>alter pluggable database all close immediate;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB MOUNTED

4 TEST MOUNTED

--打开单个pdb

SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB READ WRITE NO

4 TEST MOUNTED

--打开所有pdb

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 PDB READ WRITE NO

4 TEST READ WRITE NO

--关闭所有pdb

SQL> alter pluggable database all close immediate;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB MOUNTED

4 TEST

5.创建用户。

1).概述

在cdb默认是container=all,在cdb中只能创建全局用户;

在pdb默认是container=current,在pdb只能创建本地用户。

http://dovelauren.blog.51cto.com/9876026/1719310

如果在cdb中创建用户会在cdb和所有的pdb中创建该用户,但是pdb中的全局用户需要另外授权才能够在pdb中访问)。

2).创建全局用户

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> create user c##t identified by t;

User created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB READ WRITE NO

4 PDB1 READ WRITE NO

3).创建本地用户

SQL> alter session set container=pdb1;

Session altered.

SQL> create user t identified by t;

User created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

4 PDB1 READ WRITE NO

6.用户授权

用户授权默认情况下是只会给当前container,在cdb中也可以指定container=all,对所有open的pdb且存在该用户都进行授权

Connected.

SQL> grant connect,resource to c##t;

Grant succeeded.

SQL> grant connect,resource to c##t container=all;

Grant succeeded.

7.修改参数

在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> show parameter open_cursors

NAME TYPE VALUE

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

open_cursors integer 300

SQL> alter system set open_cursors=500;

System altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB READ WRITE NO

4 PDB1 READ WRITE NO

SQL> alter session set container=pdb1;

Session altered.

SQL> show parameter open_cursors

NAME TYPE VALUE

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

open_cursors integer 500

SQL> alter system set open_cursors=100;

System altered.

SQL> show parameter open_cursors

NAME TYPE VALUE

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

open_cursors integer 100

SQL> show con_name

CON_NAME

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

PDB1

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show parameter open_cursors

NAME TYPE VALUE

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

open_cursors integer 500

文章标题：Oracle12c日常维护
文章分享：http://scyanting.com/article/pgodce.html

Oracle12c日常维护

其他资讯