查看内容

ORACLE 12C PDB 维护基础知识介绍

  • 2020-01-05 06:52
  • 新浦京操作系统
  • Views

数据库从2.2G减肥到1.8G,论坛速度顺畅了不少

启动关闭pdb

cdb_memberspaces

SQL select * from v$version; BANNER CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0PL/SQL Release 12.1.0.1.0 - Production 0CORE 12.1.0.1.0 Production 0TNS for Linux: Version 12.1.0.1.0 - Production 0NLSRTL Version 12.1.0.1.0 - Production 0

cdb_spacecaches

SQL alter system set open_cursors=500 container=all; System altered. SQL conn sys/xifenfei@pdb1 as sysdbaConnected.SQL show parameter open_cursors; NAME TYPE VALUE------------------------------------ ----------- ------------------------------open_cursors integer 500SQL 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 fixedtables 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 100SQL conn / as sysdbaConnected.SQL show parameter open_cursors; NAME TYPE VALUE------------------------------------ ----------- ------------------------------open_cursors integer 500

这两个表貌似用不到,转到一个备份数据库里面去了,如果用到的话再转回来。

用户授权默认情况下是只会给当前container,在cdb中也可以指定container=all,对所有open的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 LISTENERVersion TNSLSNR for Linux: Version 12.1.0.1.0 - ProductionStart Date 11-MAY-2013 18:30:54Uptime 0 days 13 hr. 36 min. 8 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/grid/product/12.1/network/admin/listener.oraListener Log File /u01/app/grid/diag/tnslsnr/xifenfei/listener/alert/log.xmlListening 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 aliasAttempting 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand 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 ProductionWith the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand Real Application Testing options SQL alter session set container=pdb1; Session altered. SQL show con_name; CON_NAME------------------------------PDB1

3 2270995695 PDBORCL MOUNTED

创建用户

修改参数

ORACLE 12C版本

这里可以看到在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义

SQL startupORACLE instance started. Total System Global Area 597098496 bytesFixed Size 2291072 bytesVariable Size 272632448 bytesDatabase Buffers 314572800 bytesRedo Buffers 7602176 bytesDatabase 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 startupPluggable Database opened.SQL select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE---------- ---------- ------------------------------ ---------- 3 3313918585 PDB1 READ WRITE

ORA-65096: 公用用户名或角色名无效.

2 4066409480 PDB$SEED READ ONLY

pdb可以通过alter session container进入也可以直接通过tns方式登录

上一篇:感悟 下一篇:没有了