32.数据库的创建和维护
1.使用OEM访问数据库
--启动oem
[oracle@yuanzj.com:/home/oracle]$ emctl start dbconsole
[root@yuanzj ~]# firefox https://yuanzj.com:1158/em/console/aboutApplication
2.密码文件的管理
10g，11g没有太大差别
12c有独特的管理方式
--说明有密码文件
sys@ORCL10G 2023-02-28 22:02:07> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
Elapsed: 00:00:00.02
--说明没有密码文件
sys@ORCL10G 2023-02-28 22:02:07> select * from v$pwfile_users;
now rows selectd
Elapsed: 00:00:00.02
--有密码文件，可以远程连接
--没有密码文件，不能远程连接
--重新配置密码文件
orapwd file=$ORACLE_HOME/dbs/orapworcl10g password=Oracle_4U force=y
--禁止远程登陆
sys@ORCL10G 2023-02-28 22:02:20> show parameter remote;
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
sys@ORCL10G 2023-02-28 22:06:52> alter system set remote_login_passwordfile=none scope=spfile;
--独占模式
sys@ORCL10G 2023-02-28 22:06:52> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
--共享模式（可以使用sys登录数据库，但是不能改sys密码）
sys@ORCL10G 2023-02-28 22:06:52> alter system set remote_login_passwordfile=shared scope=spfile;
--禁止密码文件认证，只允许操作系统认证
[oracle@yuanzj.com:/home/oracle]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
sqlnet.authentication_services=all
--禁止操作系统认证，只允许密码文件认证
[oracle@yuanzj.com:/home/oracle]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
sqlnet.authentication_services=none
--Oracle11g
--密码文件区分大小写
--Oracle12c
sys@ORCL 2023-02-28 22:29:52> select * from v$pwfile_users
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_ST PASSWORD_P LAST_LOGIN LOCK_DATE EXPIR EXTERNAL_N AUTHENTI COM CON_ID
--------------- ----- ----- ----- ----- ----- ----- ---------- ---------- ---------- ---------- ----- ---------- -------- --- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD YES 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE OPEN PASSWORD NO 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE OPEN PASSWORD NO 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE OPEN PASSWORD NO 1
Elapsed: 00:00:00.00
sys@ORCL 2023-02-28 22:32:49> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB01 READ WRITE NO
sys@ORCL 2023-02-28 22:32:54> alter session set container = ORCLPDB01;
Session altered.
Elapsed: 00:00:00.00
sys@ORCL 2023-02-28 22:33:07> select * from v$pwfile_users
2 ;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_ST PASSWORD_P LAST_LOGIN LOCK_DATE EXPIR EXTERNAL_N AUTHENTI COM CON_ID
--------------- ----- ----- ----- ----- ----- ----- ---------- ---------- ---------- ---------- ----- ---------- -------- --- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD YES 0在12c中管理密码文件，需要去cdb中管理密码文件
3.参数文件的管理
--参数文件有两种
--1.查看
show parameter
select * from v$parameter;
sys@ORCL11G 2023-02-28 22:42:58> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
sys@ORCL11G 2023-02-28 22:43:04> desc v$parameter;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(4000)
DISPLAY_VALUE VARCHAR2(4000)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
ISBASIC VARCHAR2(5)
DESCRIPTION VARCHAR2(255)
UPDATE_COMMENT VARCHAR2(255)
HASH NUMBER
sys@ORCL11G 2023-02-28 22:43:09> select name,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE from v$parameter where name = 'pga_aggregate_target';
NAME ISSYS_MOD ISINS
-------------------------------------------------------------------------------- --------- -----
pga_aggregate_target IMMEDIATE TRUE
Elapsed: 00:00:00.00
--ISSYS_MODIFIABLE：表示立即生效
--ISINSTANCE_MODIFIABLE：表示可以修改
--改到内存
sys@ORCL11G 2023-02-28 22:46:22> alter system set pga_aggregate_target = 120m scope=memory;
System altered.
Elapsed: 00:00:00.01
--改到参数文件
sys@ORCL11G 2023-02-28 22:46:33> alter system set pga_aggregate_target = 120m scope=spfile;
System altered.
Elapsed: 00:00:00.01
--改到内存和参数文件
sys@ORCL11G 2023-02-28 22:46:47> alter system set pga_aggregate_target = 120m scope= both;
System altered.
Elapsed: 00:00:00.01
sys@ORCL11G 2023-02-28 22:47:03> alter system set pga_aggregate_target = 120m;
System altered.
Elapsed: 00:00:00.01会话可以改的，基本都可以改
sys@ORCL 2023-02-28 22:52:28> select PDB_UID,sid,name,value$ from pdb_spfile$;
no rows selected
Elapsed: 00:00:00.02
sys@ORCL 2023-02-28 22:53:12> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB01 READ WRITE NO
sys@ORCL 2023-02-28 22:53:27> alter session set container = ORCLPDB01;
Session altered.
Elapsed: 00:00:00.14
sys@ORCL 2023-02-28 22:53:44> alter system set open_cursors=400;
System altered.
Elapsed: 00:00:00.21
sys@ORCL 2023-02-28 22:54:17> alter session set container = cdb$root;
Session altered.
Elapsed: 00:00:00.00
sys@ORCL 2023-02-28 22:54:40> select PDB_UID,sid,name,value$ from pdb_spfile$;
PDB_UID SID NAME VALUE$
---------- --------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3873269478 * open_cursors 400
sys@ORCL 2023-02-28 23:01:18> select name,guid,con_uid from v$pdbs;
NAME GUID CON_UID
--------------- -------------------------------- ----------
PDB$SEED F48EBCF996743B67E0532802A8C010BF 182913619
ORCLPDB01 F48EE2C05E6D47CCE0532802A8C0A02A 3873269478
Elapsed: 00:00:00.00
sys@ORCL 2023-02-28 23:01:34> select pdb_uid,name from pdb_spfile$;
PDB_UID NAME
---------- ---------------
3873269478 open_cursors
Elapsed: 00:00:00.01
sys@ORCL 2023-02-28 23:01:36> alter session set container = ORCLPDB01;
Session altered.
Elapsed: 00:00:00.01
sys@ORCL 2023-02-28 23:02:36> alter system reset open_cursors;
System altered.
Elapsed: 00:00:00.10
sys@ORCL 2023-02-28 23:02:48> alter session set container = cdb$root;
Session altered.
Elapsed: 00:00:00.01
sys@ORCL 2023-02-28 23:02:56> select pdb_uid,name from pdb_spfile$;
PDB_UID NAME
---------- ---------------
3873269478 open_cursors
Elapsed: 00:00:00.00
--所有pdb参数生效
alter system set open_cursors=400 container = all;
--修改参数文件转化
create pfile from memory;
create pfile from spfile;
create spfile from pfile;4.数据库的启动和关闭
--数据库启动阶段
--实例启动阶段
--启动的四个阶段：shudown-->nomount-->mount-->open
--1.shutdown：数据库关闭阶段
--2.nomount：实例已经启动
--3.mount：此实例的控制文件已打开
--4.open：正常打开数据库
--启动到数据库的四个阶段
--startup nomount
--startup mount
--alter database open;
--oracle12c
--打开
alter pluggable database orclpdb01 open;
--关闭
alter pluggable database orclpdb01 close;5.三个版本数据库的启动和关闭
--oracle10和oracle11g启动方式一样
sys@ORCL10G 2023-02-28 23:26:23> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
Elapsed: 00:00:00.01
sys@ORCL10G 2023-02-28 23:26:39> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL10G 2023-02-28 23:26:59> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Elapsed: 00:00:00.00
sys@ORCL10G 2023-02-28 23:27:09> startup nomount;
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
sys@ORCL10G 2023-02-28 23:27:15> alter database mount;
Database altered.
Elapsed: 00:00:04.08
sys@ORCL10G 2023-02-28 23:27:30> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
Elapsed: 00:00:00.00
sys@ORCL10G 2023-02-28 23:27:39> alter database open;
Database altered.
Elapsed: 00:00:00.96
sys@ORCL10G 2023-02-28 23:27:46> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
Elapsed: 00:00:00.00
sys@ORCL10G 2023-02-28 23:27:48>6.使用grid管理数据库的启动和关闭
#查看数据库状态
srvctl status database -d orcl
#启动数据库
srvctl start database -d orcl
#停止数据库
srvctl stop database -d orcl
#增加数据库
srvctl add database -d orcl -o /u01/oracle/ora11g7.动态性能视图
v$instance
v$database
v$access
v$sql
v$session
v$process
v$bgprocess
v$datafile
v$tempfile
v$controlfile
v$logfile
v$archived_log
v$standby_log
v$log
dba_tables
all_tables
user_tables
dba_objects
dba_users
