第五章:管理数据库实例
一:大纲
- 启动和关闭数据库
- 启动和关闭pdb
- 管理cdb 和pdb
- cdb体系结构
二:启动和关闭数据库
1:数据库的几种模式
- 四种
[root@oracle ~]# su - oracle [oracle@oracle ~]$ sqlplus / as sysdba; SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 26 22:41:22 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> shutdown abort ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 1509949440 bytes Fixed Size 2924640 bytes Variable Size 973082528 bytes Database Buffers 520093696 bytes Redo Buffers 13848576 bytes SQL> SQL> SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl STARTED SQL> alter database mount; Database altered. SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl MOUNTED SQL> alter database open; Database altered. SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL>----打开pdb数据库 ---查看pdbs 数据库信息 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED SQL> alter pluggable database proc open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC READ WRITE NO SQL>
2:startup 几种模式
•NOMOUNT - 在不安装数据库的情况下启动实例。这不允许访问数据库,通常创建数据库或重新创建控制文件。
•MOUNT - 启动实例并挂载数据库,但保持关闭状态。此状态允许某些DBA活动,但不允许对数据库的一般访问。
•OPEN - 启动实例,然后挂载并打开数据库
•FORCE - 在强制关闭之后启动实例
•OPEN RECOVER- 启动实例并立即开始完整的介质恢复
3:nomount
[oracle@oracle dbca]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Dec 23 22:04:00 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 1509949440 bytes Fixed Size 2924640 bytes Variable Size 973082528 bytes Database Buffers 520093696 bytes Redo Buffers 13848576 bytes SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl STARTED SQL>
• 参数文件顺序
1.srvctl config database
2.spfileORACLE_SID.ora
3.spfile.ora
4.initORACLE_SID.ora
STARTUP NOMOUNT;
STARTUP PFILE = /u01/oracle/dbs/init.ora
1:参数文件访问位置:
[root@oracle ~]# su - oracle [oracle@oracle ~]$ cd $ORACLE_HOME [oracle@oracle dbhome_1]$ ls addnode cdata ctx demo hs jdbc log oc4j oracore perl R root.sh sqlpatch ucp apex cfgtoollogs cv diagnostics install jdk md odbc oraInst.loc plsql racg scheduler sqlplus usm assistants clone dbs dmu instantclient jlib mgw olap ord plugins rdbms slax srvm utl bin crs dc_ocm dv inventory ldap network OPatch oui precomp relnotes sqldeveloper suptools wwg ccr css deinstall has javavm lib nls opmn owm QOpatch rest sqlj sysman xdk [oracle@oracle dbhome_1]$ cd dbs [oracle@oracle dbs]$ ls hc_orcl.dat init.ora lkORCL orapworcl spfileorcl.ora [oracle@oracle dbs]$ pwd /u01/app/oracle/product/12.1.0/dbhome_1/dbs [oracle@oracle dbs]$
2:静态参数文件的创建
SQL> ho ls /tmp/ _cafenv-appconfig_ yum_save_tx-2018-12-22-15-37wc3OZs.yumtx yum_save_tx-2018-12-23-14-07JstF3i.yumtx CVU_12.1.0.2.0_oracle yum_save_tx-2018-12-22-15-37wvRojt.yumtx yum_save_tx-2018-12-23-14-07nXfj9t.yumtx hsperfdata_oracle yum_save_tx-2018-12-22-15-37xvsZ9M.yumtx yum_save_tx-2018-12-23-14-07_SfhZ4.yumtx keyring-rlnZrh yum_save_tx-2018-12-23-14-03DnG2_H.yumtx yum_save_tx-2018-12-23-14-07vXwHRm.yumtx OraInstall2018-12-23_11-35-55AM yum_save_tx-2018-12-23-14-0420WrQT.yumtx yum_save_tx-2018-12-23-14-07xNr6Qj.yumtx OraInstall2018-12-23_11-46-21AM yum_save_tx-2018-12-23-14-04227Z6Z.yumtx yum_save_tx-2018-12-23-14-07yc7cRF.yumtx orbit-gdm yum_save_tx-2018-12-23-14-042ZI1nl.yumtx yum_save_tx-2018-12-23-14-082h_wkD.yumtx orbit-root yum_save_tx-2018-12-23-14-047g7vAP.yumtx yum_save_tx-2018-12-23-14-08CkS7U7.yumtx pulse-8bowRuO9O3ko yum_save_tx-2018-12-23-14-04CWmOxX.yumtx yum_save_tx-2018-12-23-14-08cM7ZJN.yumtx pulse-Q6EtuncfXpcx yum_save_tx-2018-12-23-14-04dORK2a.yumtx yum_save_tx-2018-12-23-14-08kL8eP2.yumtx virtual-root.1FWVlD yum_save_tx-2018-12-23-14-04E3_5Jj.yumtx yum_save_tx-2018-12-23-14-08kqk5la.yumtx virtual-root.O4MF9B yum_save_tx-2018-12-23-14-04Etqjt2.yumtx yum_save_tx-2018-12-23-14-08TIdaJu.yumtx vmware-config-1114.0 yum_save_tx-2018-12-23-14-04ilXI6O.yumtx yum_save_tx-2018-12-23-14-08VQpoDX.yumtx vmware-config-6775.0 yum_save_tx-2018-12-23-14-04J445aT.yumtx yum_save_tx-2018-12-23-14-08wYGnOQ.yumtx VMwareDnD yum_save_tx-2018-12-23-14-04qKDPJE.yumtx yum_save_tx-2018-12-23-14-08XaI3fP.yumtx vmware-root yum_save_tx-2018-12-23-14-04q_n_4G.yumtx yum_save_tx-2018-12-23-14-09GS0ONQ.yumtx yum.log yum_save_tx-2018-12-23-14-04QNh28N.yumtx yum_save_tx-2018-12-23-14-09Jr74Pq.yumtx yum_save_tx-2018-12-22-15-37CAjnNS.yumtx yum_save_tx-2018-12-23-14-04TK_jtf.yumtx yum_save_tx-2018-12-23-14-09tz0MH3.yumtx yum_save_tx-2018-12-22-15-37cy9aGN.yumtx yum_save_tx-2018-12-23-14-05wtVBlA.yumtx yum_save_tx-2018-12-23-14-09XpDi3S.yumtx yum_save_tx-2018-12-22-15-37idXRQz.yumtx yum_save_tx-2018-12-23-14-071n9cpU.yumtx yum_save_tx-2018-12-23-14-09Z2W62r.yumtx yum_save_tx-2018-12-22-15-37jHkoI_.yumtx yum_save_tx-2018-12-23-14-076BwffJ.yumtx yum_save_tx-2018-12-23-14-2681kF3G.yumtx yum_save_tx-2018-12-22-15-37kaZtPp.yumtx yum_save_tx-2018-12-23-14-078NoG_D.yumtx yum_save_tx-2018-12-23-14-26lrpKWL.yumtx yum_save_tx-2018-12-22-15-37nSyI9M.yumtx yum_save_tx-2018-12-23-14-07aAp5XV.yumtx yum_save_tx-2018-12-23-14-31g_VDqp.yumtx yum_save_tx-2018-12-22-15-37r9ISym.yumtx yum_save_tx-2018-12-23-14-07dMVhRz.yumtx yum_save_tx-2018-12-23-14-34EZ7DqJ.yumtx yum_save_tx-2018-12-22-15-37rtXvb5.yumtx yum_save_tx-2018-12-23-14-07FXx2Kg.yumtx yum_save_tx-2018-12-23-14-37iS90WG.yumtx yum_save_tx-2018-12-22-15-37TxBT31.yumtx yum_save_tx-2018-12-23-14-07HKYJZk.yumtx yum_save_tx-2018-12-23-14-47mNV9hQ.yumtx yum_save_tx-2018-12-22-15-37utghPB.yumtx yum_save_tx-2018-12-23-14-07idve1R.yumtx yum_save_tx-2018-12-23-14-47XkSmHY.yumtx SQL> create pfile ='/tmp/initorcl.ora' from spfile; File created. SQL> ho ls /tmp/ _cafenv-appconfig_ yum_save_tx-2018-12-22-15-37wc3OZs.yumtx yum_save_tx-2018-12-23-14-07nXfj9t.yumtx CVU_12.1.0.2.0_oracle yum_save_tx-2018-12-22-15-37wvRojt.yumtx yum_save_tx-2018-12-23-14-07_SfhZ4.yumtx hsperfdata_oracle yum_save_tx-2018-12-22-15-37xvsZ9M.yumtx yum_save_tx-2018-12-23-14-07vXwHRm.yumtx initorcl.ora yum_save_tx-2018-12-23-14-03DnG2_H.yumtx yum_save_tx-2018-12-23-14-07xNr6Qj.yumtx keyring-rlnZrh yum_save_tx-2018-12-23-14-0420WrQT.yumtx yum_save_tx-2018-12-23-14-07yc7cRF.yumtx OraInstall2018-12-23_11-35-55AM yum_save_tx-2018-12-23-14-04227Z6Z.yumtx yum_save_tx-2018-12-23-14-082h_wkD.yumtx OraInstall2018-12-23_11-46-21AM yum_save_tx-2018-12-23-14-042ZI1nl.yumtx yum_save_tx-2018-12-23-14-08CkS7U7.yumtx orbit-gdm yum_save_tx-2018-12-23-14-047g7vAP.yumtx yum_save_tx-2018-12-23-14-08cM7ZJN.yumtx orbit-root yum_save_tx-2018-12-23-14-04CWmOxX.yumtx yum_save_tx-2018-12-23-14-08kL8eP2.yumtx pulse-8bowRuO9O3ko yum_save_tx-2018-12-23-14-04dORK2a.yumtx yum_save_tx-2018-12-23-14-08kqk5la.yumtx pulse-Q6EtuncfXpcx yum_save_tx-2018-12-23-14-04E3_5Jj.yumtx yum_save_tx-2018-12-23-14-08TIdaJu.yumtx virtual-root.1FWVlD yum_save_tx-2018-12-23-14-04Etqjt2.yumtx yum_save_tx-2018-12-23-14-08VQpoDX.yumtx virtual-root.O4MF9B yum_save_tx-2018-12-23-14-04ilXI6O.yumtx yum_save_tx-2018-12-23-14-08wYGnOQ.yumtx vmware-config-1114.0 yum_save_tx-2018-12-23-14-04J445aT.yumtx yum_save_tx-2018-12-23-14-08XaI3fP.yumtx vmware-config-6775.0 yum_save_tx-2018-12-23-14-04qKDPJE.yumtx yum_save_tx-2018-12-23-14-09GS0ONQ.yumtx VMwareDnD yum_save_tx-2018-12-23-14-04q_n_4G.yumtx yum_save_tx-2018-12-23-14-09Jr74Pq.yumtx vmware-root yum_save_tx-2018-12-23-14-04QNh28N.yumtx yum_save_tx-2018-12-23-14-09tz0MH3.yumtx yum.log yum_save_tx-2018-12-23-14-04TK_jtf.yumtx yum_save_tx-2018-12-23-14-09XpDi3S.yumtx yum_save_tx-2018-12-22-15-37CAjnNS.yumtx yum_save_tx-2018-12-23-14-05wtVBlA.yumtx yum_save_tx-2018-12-23-14-09Z2W62r.yumtx yum_save_tx-2018-12-22-15-37cy9aGN.yumtx yum_save_tx-2018-12-23-14-071n9cpU.yumtx yum_save_tx-2018-12-23-14-2681kF3G.yumtx yum_save_tx-2018-12-22-15-37idXRQz.yumtx yum_save_tx-2018-12-23-14-076BwffJ.yumtx yum_save_tx-2018-12-23-14-26lrpKWL.yumtx yum_save_tx-2018-12-22-15-37jHkoI_.yumtx yum_save_tx-2018-12-23-14-078NoG_D.yumtx yum_save_tx-2018-12-23-14-31g_VDqp.yumtx yum_save_tx-2018-12-22-15-37kaZtPp.yumtx yum_save_tx-2018-12-23-14-07aAp5XV.yumtx yum_save_tx-2018-12-23-14-34EZ7DqJ.yumtx yum_save_tx-2018-12-22-15-37nSyI9M.yumtx yum_save_tx-2018-12-23-14-07dMVhRz.yumtx yum_save_tx-2018-12-23-14-37iS90WG.yumtx yum_save_tx-2018-12-22-15-37r9ISym.yumtx yum_save_tx-2018-12-23-14-07FXx2Kg.yumtx yum_save_tx-2018-12-23-14-47mNV9hQ.yumtx yum_save_tx-2018-12-22-15-37rtXvb5.yumtx yum_save_tx-2018-12-23-14-07HKYJZk.yumtx yum_save_tx-2018-12-23-14-47XkSmHY.yumtx yum_save_tx-2018-12-22-15-37TxBT31.yumtx yum_save_tx-2018-12-23-14-07idve1R.yumtx yum_save_tx-2018-12-22-15-37utghPB.yumtx yum_save_tx-2018-12-23-14-07JstF3i.yumtx SQL> ho more /tmp/initorcl.ora orcl.__data_transfer_cache_size=0 orcl.__db_cache_size=553648128 orcl.__java_pool_size=16777216 orcl.__large_pool_size=33554432 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=603979776 orcl.__sga_target=905969664 orcl.__shared_io_pool_size=50331648 orcl.__shared_pool_size=234881024 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='12.1.0.2.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.enable_pluggable_database=true *.memory_target=1440m *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' SQL>
3:查看 spfile 文件参数信息
SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 /dbhome_1/dbs/spfileorcl.ora SQL>
4:从pfile 创建 spfile
4:nomount
•在默认位置找服务器参数文件,如果未找到,则搜索文本初始化参数文件(除指定SPFILE或PFILE参数);
•读取参数文件以确定初始化参数的值;
•根据初始化参数设置分配SGA;
•启动Oracle后台进程;
•打开警报日志和跟踪文件,并将所有显式参数设置写入警报日志。
5:mount
•实例将从CONTROL_FILES初始化参数中指定的控制文件的名称中打开文件。Oracle数据库读取控制文件以便打开数据库时访问数据文件的名称和联机重做日志文件
•启用和禁用归档模式
•执行完整的数据库恢复
•强制完全数据库缓存模式:
•在默认缓存模式下,当用户查询大型表时,Oracle数据库并不会缓存基础数据,因为这样做可能会从缓冲区缓存中删除更多有用的数据。从Oracle Database 12 c第1版(12.1.0.2)开始,如果Oracle数据库实例确定有足够的空间来将整个数据库缓存在缓冲区缓存中且对数据库没有什么负面影响,那么实例会自动缓存缓冲区缓存中的完整数据库。
•ALTER DATABASE FORCE FULL DATABASE CACHING;
6:open
•除UNDO表空间以外的表空间中打开在线数据文件
如果关闭数据库时表空间处于脱机状态,则在重新打开数据库时,表空间及其相应的数据文件将处于脱机状态;
•获取UNDO表空间
如果存在多个撤消表空间,则由UNDO_TABLESPACE初始化参数指定。未设置此参数,则选择第一个可用的;
•打开在线重做日志
7:修改数据库可用性
•ALTER DATABASE MOUNT;
•ALTER DATABASE OPEN;
具有CREATE SESSION系统特权的任何有效Oracle数据库用户都可以连接到数据库
•ALTER DATABASE OPEN READ ONLY/ READ WRITE;
READ ONLY(只读)
READ WRITE(读写)
•ALTER SYSTEM DISABLE RESTRICTED SESSION;
•执行数据导出或导入
•执行数据加载(使用SQL * Loader)
•暂时阻止典型用户使用数据
•执行某些迁移或升级操作
以RESTRICTED模式打开数据库只允许数据库访问具有CREATE SESSION和RESTRICTED SESSION系统权限的用户。只有数据库管理员才具有RESTRICTED SESSION系统特权。此外,当实例处于受限模式时,数据库管理员无法通过Oracle Net侦听器远程访问实例,但只能从运行该实例的系统本地访问该实例。
8:SHUTDOWN
•NORMAL
1.不允许新的连接(不需要实例恢复)
2.在关闭之前会等待所有当前连接的用户断开连接
•ABORT
1.不允许新连接,也不允许启动新事务,未回滚未提交的事务
2.Oracle Database正在处理的当前客户端SQL语句将立即终止。
•Oracle数据库不会等待当前连接到数据库的用户断开,数据库隐式断开所有连接的用户。(需要实例恢复)•TRANSACTIONAL
1. 不允许新连接,也不允许启动新事务(不需要实例恢复)
2.完成所有事务后,任何仍连接到实例的客户端都将断开连接。(immediate)防止客户端丢失事务,不需要用户注销。
•IMMEDIATE
1.不允许新的连接(不需要实例恢复)
2.终止所有正在执行的SQL语句并断开用户连接活动事务将终止,并且将回滚未提交的事务。(长事务会影响)
三:启动和关闭PDB
1:SHUTDOWN
•TRANSACTIONAL
1. 不允许新连接,也不允许启动新事务(不需要实例恢复)
2.完成所有事务后,任何仍连接到实例的客户端都将断开连接。(immediate)防止客户端丢失事务,不需要用户注销。
•IMMEDIATE
1.不允许新的连接(不需要实例恢复)
2.终止所有正在执行的SQL语句并断开用户连接活动事务将终止,并且将回滚未提交的事务。(长事务会影响)
四:管理CDB和PDB
1:CDB组件
•CDB$ROOT
存储Oracle提供的元数据和普通用户。示例:Oracle提供的PL / SQL包的源代码。普通用户是每个容器中已知的数据库用户。
•PDB$SEED:
用于创建新的PDB模板。不能在种子中添加对象或修改对象,CDB只有一个SEED。
•PDBS:
一个CDB可以用多个PDB(非CDB相同)
•每个CDB只包含一个root,一个seed,零个或者多个用户创建的PDBS
2:CDB
SQL> startup ORACLE instance started. Total System Global Area 1509949440 bytes Fixed Size 2924640 bytes Variable Size 973082528 bytes Database Buffers 520093696 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> select con_id,name ,open_mode from v$containers; CON_ID NAME OPEN_MODE ---------- ------------------------------ ---------- 1 CDB$ROOT READ WRITE 2 PDB$SEED READ ONLY 3 PROC MOUNTED SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED SQL>SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 /dbhome_1/dbs/spfileorcl.ora SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cell_offloadgroup_name string db_file_name_convert string db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string orcl lock_name_space string log_file_name_convert string pdb_file_name_convert string processor_group_name string service_names string orcl SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 1440M sga_target big integer 0 unified_audit_sga_queue_size integer 1048576 SQL>
---查看当前连接的数据库信息 SQL> select sys_context('userenv','con_name') from dual; SYS_CONTEXT('USERENV','CON_NAME') -------------------------------------------------------------------------------- CDB$ROOT ---连接切换到 proc数据库 SQL> alter session set container=proc; Session altered. ---查看当前连接的数据库信息 SQL> select sys_context('userenv','con_name') from dual; SYS_CONTEXT('USERENV','CON_NAME') -------------------------------------------------------------------------------- PROC ---数据库切换到 CDB$ROOT数据库 SQL> alter session set container=CDB$ROOT; Session altered. ---查看目前pdbs打开情况 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED ---查看数据库当前的连接信息 SQL> select sys_context('userenv','con_name') from dual; SYS_CONTEXT('USERENV','CON_NAME') -------------------------------------------------------------------------------- CDB$ROOT SQL>
3:管理CDB
•SELECT SYS_CONTEXT('USERENV','CON_NAME')FROM DUAL;
•ALTER SESSION SET CONTAINER = container_name [SERVICE = service_name ]
•conn username@tnsname
•select CON_ID,NAME,OPEN_MODE from v$containers;
•show pdbs
•ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = CURRENT;##修改参数
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PROC MOUNTED ----切换连接方式:由 pdb数据库的连接切换到 CDB$ROOT的数据库 SQL> alter session set container=CDB$ROOT; Session altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED SQL>
4:管理PDB
•– Alter pluggable database xxx close;
•– Alter pluggable database xxx open;
•– Alter session set container=xx;
•– Shutdown imemdiate
•– Alter database open
•ALTER PLUGGABLE DATABASE salespdb SAVE/ DISCARD STATE;(保存状态)---删除某个数据库日期 pdb 及其文件
•drop pluggable database pdb1 including datafiles;SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED --打开pdb 数据库 SQL> alter pluggable database proc open ; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC READ WRITE NO
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC READ WRITE NO ---关闭pdb 数据库 SQL> alter pluggable database proc close; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED SQL>
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED ---在命令行切换连接方式:从CDB$ROOT数据库切换到 pdb模式下PROC数据库 SQL> alter session set container=proc; Session altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PROC MOUNTED SQL>
---切换 系统 用户 [root@oracle ~]# su - oracle ---登录 sqlplus [oracle@oracle ~]$ sqlplus / as sysdba; SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 27 20:36:49 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ---查看数据库示例状态 SQL> select instance_name ,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN ---显示 数据库容器的状态 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED ---关闭数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. ---启动数据库 SQL> startup ORACLE instance started. Total System Global Area 1509949440 bytes Fixed Size 2924640 bytes Variable Size 973082528 bytes Database Buffers 520093696 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. ----查看数据库容器状态 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC MOUNTED ---proc 容器在数据库重启后默认是关闭状态 -- 打开 数据库容器 proc SQL> alter pluggable database proc open; Pluggable database altered. --设置 数据库容器 proc 默认为开启状态 SQL> alter pluggable database proc save state; Pluggable database altered. ---关闭数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. --启动数据库 SQL> startup ORACLE instance started. Total System Global Area 1509949440 bytes Fixed Size 2924640 bytes Variable Size 973082528 bytes Database Buffers 520093696 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. --查看数据库 容器状态信息 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PROC READ WRITE NO ---此时:数据库日期proc默认是随数据库的启动而保持开着状态 SQL>
5:PDB
创建pdb 的几种创建方式
五: 查看运行日志文件
[oracle@oracle ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/ [oracle@oracle trace]$ ls alert_orcl.log orcl_dbrm_6772.trm orcl_gen0_6874.trm orcl_lgwr_6890.trm orcl_mmon_6906.trm orcl_ora_6835.trm orcl_ora_7317.trm orcl_vkrm_6721.trm orcl_aqpc_7003.trc orcl_dbrm_6882.trc orcl_lg00_6731.trc orcl_lgwr_7407.trc orcl_mmon_7423.trc orcl_ora_6844.trc orcl_ora_7378.trc orcl_vkrm_6774.trc orcl_aqpc_7003.trm orcl_dbrm_6882.trm orcl_lg00_6731.trm orcl_lgwr_7407.trm orcl_mmon_7423.trm orcl_ora_6844.trm orcl_ora_7378.trm orcl_vkrm_6774.trm orcl_aqpc_7451.trc orcl_dbrm_7399.trc orcl_lg00_6784.trc orcl_m000_6681.trc orcl_ora_6603.trc orcl_ora_6856.trc orcl_ora_7430.trc orcl_vkrm_6884.trc orcl_aqpc_7451.trm orcl_dbrm_7399.trm orcl_lg00_6784.trm orcl_m000_6681.trm orcl_ora_6603.trm orcl_ora_6856.trm orcl_ora_7430.trm orcl_vkrm_6884.trm orcl_cjq0_6934.trc orcl_dbw0_6631.trc orcl_lg00_6894.trc orcl_m000_6694.trc orcl_ora_6656.trc orcl_ora_6859.trc orcl_ora_7439.trc orcl_vkrm_7401.trc orcl_cjq0_6934.trm orcl_dbw0_6631.trm orcl_lg00_6894.trm orcl_m000_6694.trm orcl_ora_6656.trm orcl_ora_6859.trm orcl_ora_7439.trm orcl_vkrm_7401.trm orcl_cjq0_7453.trc orcl_dbw0_6725.trc orcl_lg01_6735.trc orcl_m000_6813.trc orcl_ora_6675.trc orcl_ora_6862.trc orcl_ora_7456.trc orcl_vktm_6613.trc orcl_cjq0_7453.trm orcl_dbw0_6725.trm orcl_lg01_6735.trm orcl_m000_6813.trm orcl_ora_6675.trm orcl_ora_6862.trm orcl_ora_7456.trm orcl_vktm_6613.trm orcl_ckpt_6635.trc orcl_dbw0_6778.trc orcl_lg01_6788.trc orcl_m000_7344.trc orcl_ora_6698.trc orcl_ora_6913.trc orcl_ora_7786.trc orcl_vktm_6707.trc orcl_ckpt_6635.trm orcl_dbw0_6778.trm orcl_lg01_6788.trm orcl_m000_7344.trm orcl_ora_6698.trm orcl_ora_6913.trm orcl_ora_7786.trm orcl_vktm_6707.trm orcl_ckpt_6729.trc orcl_dbw0_6888.trc orcl_lg01_6898.trc orcl_m000_7784.trc orcl_ora_6699.trc orcl_ora_6922.trc orcl_tt00_6823.trc orcl_vktm_6760.trc orcl_ckpt_6729.trm orcl_dbw0_6888.trm orcl_lg01_6898.trm orcl_m000_7784.trm orcl_ora_6699.trm orcl_ora_6922.trm orcl_tt00_6823.trm orcl_vktm_6760.trm orcl_ckpt_6782.trc orcl_dbw0_7405.trc orcl_lgwr_6633.trc orcl_mmon_6743.trc orcl_ora_6750.trc orcl_ora_7004.trc orcl_tt00_6926.trc orcl_vktm_6870.trc orcl_ckpt_6782.trm orcl_dbw0_7405.trm orcl_lgwr_6633.trm orcl_mmon_6743.trm orcl_ora_6750.trm orcl_ora_7004.trm orcl_tt00_6926.trm orcl_vktm_6870.trm orcl_ckpt_6892.trc orcl_gen0_6711.trc orcl_lgwr_6727.trc orcl_mmon_6796.trc orcl_ora_6752.trc orcl_ora_7156.trc orcl_tt00_7443.trc orcl_vktm_7387.trc orcl_ckpt_6892.trm orcl_gen0_6711.trm orcl_lgwr_6727.trm orcl_mmon_6796.trm orcl_ora_6752.trm orcl_ora_7156.trm orcl_tt00_7443.trm orcl_vktm_7387.trm orcl_ckpt_7409.trc orcl_gen0_6764.trc orcl_lgwr_6780.trc orcl_mmon_6805.trc orcl_ora_6803.trc orcl_ora_7290.trc orcl_vkrm_6627.trc orcl_ckpt_7409.trm orcl_gen0_6764.trm orcl_lgwr_6780.trm orcl_mmon_6805.trm orcl_ora_6803.trm orcl_ora_7290.trm orcl_vkrm_6627.trm orcl_dbrm_6772.trc orcl_gen0_6874.trc orcl_lgwr_6890.trc orcl_mmon_6906.trc orcl_ora_6835.trc orcl_ora_7317.trc orcl_vkrm_6721.trc [oracle@oracle trace]$
==========================================================================================================================================================
学问:纸上得来终觉浅,绝知此事要躬行
为事:工欲善其事,必先利其器。
态度:道阻且长,行则将至;行而不辍,未来可期
.....................................................................
------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客,仅做学习讨论之用,不做商业用途。
* 如有侵权,马上联系我,我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处! ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/