70. oracle基本操作¶
#以下操作均在oracle11gxe上！
0. 登录测试¶
#xe本地登录测试
[root@152 ~]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 10:40:29 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
[root@152 ~]#
#本地登录测试2（成功）
su - oracle
-bash-4.2$ sqlplus system/123
SQL*Plus: Release 11.2.0.2.0 Production on Fri May 21 13:40:07 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL>
#远程登录测试（失败）
[root@152 ~]# sqlplus system/123@192.168.178.152:1521@xe
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 10:41:51 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
[root@152 ~]#
#已找到原因，必须要切换到oracle用户下！！！不可以在root目录
[root@152 etc]# su - oracle
Last login: Mon May 17 09:19:15 CST 2021
-bash: ulimit: max user processes: cannot modify limit: Operation not permitted
-bash-4.2$
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 11:05:13 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
1. 安装后的基本设置¶
1.1 查看sid¶
ps -ef|grep smon
[root@152 ~]# ps -ef|grep smon
oracle 9798 1 0 01:45 ? 00:00:02 xe_smon_XE
root 83213 83042 0 10:39 pts/1 00:00:00 grep --color=auto smon
[root@152 ~]#
1.2 查看oracle数据库是否可用¶
#登录后
select open_mode from v$database;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
1.3 解除用户限制¶
#查看
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
#修改
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
#1.查看
SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD
180
SQL>
#2.修改
SQL> alter profile default limit password_life_time unlimited;
Profile altered.
#3.再次查看
SQL> select * from dba_profiles where profile='DEFAULT' and resource_name='PASSW ORD_LIFE_TIME';
PROFILE RESOURCE_NAME RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT PASSWORD_LIFE_TIME PASSWORD
UNLIMITED
SQL>
1.4 开归档¶
#1.创建归档目录
mkdir -p /u01/arch
#2.修改
alter system set log_archive_dest_1='location=/u01/arch/';
#3.关闭oracle
shutdown immediate;
#4.启动到mount阶段
startup mount;
#5.开启归档
alter system archivelog;
#6.启动到open阶段
alter database open;
[root@152 ~]# ls /u01
app
[root@152 ~]# mkdir /u01/arch -p
[root@152 ~]#
[root@152 ~]# ls /u01
app arch
[root@152 ~]#
[root@152 ~]# sqlplus system/123@XE
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 10:46:58 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12532: TNS:invalid argument
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> alter system set log_archive_dest_1='location=/u01/arch/';
System altered.
SQL>
SQL> shutdown immediate;
ORA-01031: insufficient privileges
#使用oracle用户登录后，再次测试
[root@152 etc]# su - oracle
Last login: Mon May 17 09:19:15 CST 2021
-bash: ulimit: max user processes: cannot modify limit: Operation not permitted
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 11:07:56 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> alter system set log_archive_dest_1='location=/u01/arch/';
System altered.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 268436720 bytes
Database Buffers 130023424 bytes
Redo Buffers 4333568 bytes
Database mounted.
SQL>
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL>
#命令写错了
SQL> alter system archivelog;
alter system archivelog
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/arch/
Oldest online log sequence 2
Current log sequence 3
SQL>
SQL> alter database archivelog;
Database altered.
SQL>
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch/
Oldest online log sequence 2
Next log sequence to archive 3
Current log sequence 3
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
2. 网络管理¶
2.1 介绍¶
listener.ora #服务器端配置文件
tnsnames.ora #客户端用来解析的配置文件
2.2 listener配置查看------被动注册¶
#监听默认是被动注册
cd $ORACLE_HOME/network/admin
cat listener.ora
#查看监听状态
lsnrctl status
lsnrctl stop
lsnrctl status
lsnrctl start
lsnrctl status
-bash-4.2$ cd $ORACLE_HOME/network/admin
-bash-4.2$ pwd
/u01/app/oracle/product/11.2.0/xe/network/admin
-bash-4.2$
-bash-4.2$ ls
listener.ora samples tnsnames.ora
-bash-4.2$
-bash-4.2$ id
uid=1000(oracle) gid=1000(dba) groups=1000(dba)
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat listener.ora
# listener.ora Network Configuration File:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = 152)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
-bash-4.2$
-bash-4.2$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:24:55
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 17-MAY-2021 09:18:08
Uptime 3 days 2 hr. 6 min. 47 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/152/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.152)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$
-bash-4.2$
-bash-4.2$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:26:25
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
The command completed successfully
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:26:33
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=152)(PORT=1521)))
TNS-12532: TNS:invalid argument
TNS-12560: TNS:protocol adapter error
TNS-00502: Invalid argument
Linux Error: 22: Invalid argument
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:26:52
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/152/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.152)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 20-MAY-2021 11:26:52
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/152/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.152)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$
-bash-4.2$
-bash-4.2$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:26:58
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 20-MAY-2021 11:26:52
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/152/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.152)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$
-bash-4.2$
2.3 listener配置------主动注册(使用图形化工具-netmgr（略）)¶
vim listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = XE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(SID_NAME = XE)
)
)
lsnrctl stop
lsnrctl start
lsnrctl status
-bash-4.2$ vim listener.ora
-bash-4.2$
-bash-4.2$
-bash-4.2$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:37:47
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
The command completed successfully
-bash-4.2$
-bash-4.2$
-bash-4.2$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:37:54
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listene r.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/152/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.152)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 20-MAY-2021 11:37:54
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listen er.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/152/listener/alert/log.xm l
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.152)(PORT=1521)))
Services Summary...
Service "XE" has 1 instance(s).
Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$
-bash-4.2$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:38:03
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 20-MAY-2021 11:37:54
Uptime 0 days 0 hr. 0 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listen er.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/152/listener/alert/log.xm l
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.152)(PORT=1521)))
Services Summary...
Service "XE" has 1 instance(s).
Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat listener.ora
# listener.ora Network Configuration File:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = XE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(SID_NAME = XE)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = 152)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
2.4 tnsnames.ora配置——图形化工具——netca(略)¶
-bash-4.2$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 152)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
-bash-4.2$
-bash-4.2$ cp tnsnames.ora tnsnames.ora.bak
-bash-4.2$
-bash-4.2$ vim tnsnames.ora
-bash-4.2$
-bash-4.2$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.152)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
-bash-4.2$
-bash-4.2$
#依旧不行
-bash-4.2$ sqlplus system/123@XE
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 11:46:14 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
Enter user-name:
#ping一下
-bash-4.2$ tnsping XE
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 20-MAY-2021 11:48:30
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.152)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = XE)))
TNS-12541: TNS:no listener
-bash-4.2$
2.5 监听常用命令¶
lsnrctl status
lsnrctl stop
lsnrctl start
tnsping XE
netca
netmgr
3. oracle用户管理¶
3.1 概念介绍¶
#用户：
对数据库的访问需要以适当的身份通过验证，这就是用户的作用；
每个oracle用户都有自己的用户名和密码，并且拥有他们所创建的任意表、视图和其他资源。
需要注意的是，新创建的用户连最基本的访问登录oracle的权限都没有，所以后续需要授予不同的权限和角色来达成不同的操作目的；
#实体权限：
权限一般分为系统权限和对象权限
系统权限可以通俗的理解为将数据库对象从无到有或从有到无的创建操作，比如说创建和删除表、视图、存储过程、session等这些操作都可以认为是系统权限。
对象权限赋予了你在不同的数据库对象上操作的能力，比如delete权限允许你对表和视图删除行，selete权限允许你对表、视图、序列等进行查询操作！
#角色：
如果多个新建用户需要后续赋予不同的权限，一般方法是需要将10个不懂得权限分别列出来进行授予，不但麻烦而且重用性不高，角色的出现就是为了解决这个问题。
oracle角色是一组权限的集合。
正常情况下我们可以授予角色一些权限，然后将角色分配给适当的用户。
3.2 权限分类¶
#管理相关权限
DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构
RESOURCE：拥有resource权限的用户，只可以创建对象，不可以创建数据库结构
CONNECT：拥有connect权限的用户可以登录oracle，不可以创建实体，不可以创建数据库结构
#普通用户权限
select,update,insert,alter,index,delete --->all
execute ---> 执行存储过程的权限
3.3 创建用户¶
#1.创建用户的profile文件
create profile student limit #student为资源文件名
failed_login_attempts 3 #指定锁定用户的登录失败次数
password_lock_time 5 #指定用户被锁定的天数
password_life_time 30; #指定口令可用天数
#例：
create profile student limit
failed_login_attempts 3
password_lock_time 5
password_life_time 30;
#2.创建用户
create user chupeng
identified by 123
default tablespace users
temporary tablespace temp
profile student
quota 50m on users;
#3.查看表空间名
select tablespace_name from dba_tablespaces;
#4.查看用户默认表空间、临时表空间
select username,default_tablespace,temporary_tablespace from dba_users;
#5.查看系统配置文件名
select * from dba_profiles;
#6.查看用户会话信息
select username,sid,serial#,machine from v$session;
#7.删除用户会话信息
alter system kill session 'sid,serial#';
#8.设置显示格式
set linesize 200
#9.执行上一条语句
/
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 13:19:17 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create profile student limit
failed_login_attempts 3
password_lock_time 5
password_life_time 30; 2 3 4
Profile created.
SQL>
SQL> create user chupeng
identified by 123
default tablespace users
temporary tablespace temp
profile student
quota 50m on users; 2 3 4 5 6
User created.
SQL>
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SQL>
SQL>
SQL>
SQL> select username,default_tablespace,temporary_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
SYSTEM SYSTEM
TEMP
SYS SYSTEM
TEMP
ANONYMOUS SYSAUX
TEMP
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
CHUPENG USERS
TEMP
APEX_PUBLIC_USER SYSTEM
TEMP
APEX_040000 SYSAUX
TEMP
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
XS$NULL SYSTEM
TEMP
OUTLN SYSTEM
TEMP
XDB SYSAUX
TEMP
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
CTXSYS SYSAUX
TEMP
MDSYS SYSAUX
TEMP
FLOWS_FILES SYSAUX
TEMP
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
HR USERS
TEMP
13 rows selected.
SQL>
SQL>
SQL> set linesize 200
SQL>
SQL> /
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SYSTEM SYSTEM TEMP
SYS SYSTEM TEMP
ANONYMOUS SYSAUX TEMP
CHUPENG USERS TEMP
APEX_PUBLIC_USER SYSTEM TEMP
APEX_040000 SYSAUX TEMP
XS$NULL SYSTEM TEMP
OUTLN SYSTEM TEMP
XDB SYSAUX TEMP
CTXSYS SYSAUX TEMP
MDSYS SYSAUX TEMP
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
FLOWS_FILES SYSAUX TEMP
HR USERS TEMP
13 rows selected.
SQL>
SQL>
SQL>
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
STUDENT FAILED_LOGIN_ATTEMPTS PASSWORD 3
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
STUDENT PASSWORD_LOCK_TIME PASSWORD 5
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
STUDENT PASSWORD_LIFE_TIME PASSWORD 30
STUDENT PASSWORD_GRACE_TIME PASSWORD DEFAULT
STUDENT PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
STUDENT PASSWORD_REUSE_MAX PASSWORD DEFAULT
STUDENT PASSWORD_REUSE_TIME PASSWORD DEFAULT
STUDENT PRIVATE_SGA KERNEL DEFAULT
STUDENT CONNECT_TIME KERNEL DEFAULT
STUDENT IDLE_TIME KERNEL DEFAULT
STUDENT LOGICAL_READS_PER_CALL KERNEL DEFAULT
STUDENT LOGICAL_READS_PER_SESSION KERNEL DEFAULT
STUDENT CPU_PER_CALL KERNEL DEFAULT
STUDENT CPU_PER_SESSION KERNEL DEFAULT
STUDENT SESSIONS_PER_USER KERNEL DEFAULT
STUDENT COMPOSITE_LIMIT KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
MONITORING_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
MONITORING_PROFILE PRIVATE_SGA KERNEL DEFAULT
MONITORING_PROFILE CONNECT_TIME KERNEL DEFAULT
MONITORING_PROFILE IDLE_TIME KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
MONITORING_PROFILE CPU_PER_CALL KERNEL DEFAULT
MONITORING_PROFILE CPU_PER_SESSION KERNEL DEFAULT
MONITORING_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
MONITORING_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
48 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select username,sid,serial#,machine from v$session;
USERNAME SID SERIAL# MACHINE
------------------------------ ---------- ---------- ----------------------------------------------------------------
2 1 152
3 1 152
4 1 152
5 1 152
6 1 152
7 1 152
8 1 152
9 1 152
10 1 152
11 1 152
12 1 152
USERNAME SID SERIAL# MACHINE
------------------------------ ---------- ---------- ----------------------------------------------------------------
13 1 152
14 1 152
15 1 152
16 1 152
17 39 152
18 5 152
20 3 152
21 1 152
22 1 152
25 1 152
26 1 152
USERNAME SID SERIAL# MACHINE
------------------------------ ---------- ---------- ----------------------------------------------------------------
27 3 152
31 3 152
32 151 152
34 1 152
36 25 152
SYS 39 99 152
28 rows selected.
SQL>
SQL>
SQL> select * from dba_profiles where profile='STUDENT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
STUDENT COMPOSITE_LIMIT KERNEL DEFAULT
STUDENT SESSIONS_PER_USER KERNEL DEFAULT
STUDENT CPU_PER_SESSION KERNEL DEFAULT
STUDENT CPU_PER_CALL KERNEL DEFAULT
STUDENT LOGICAL_READS_PER_SESSION KERNEL DEFAULT
STUDENT LOGICAL_READS_PER_CALL KERNEL DEFAULT
STUDENT IDLE_TIME KERNEL DEFAULT
STUDENT CONNECT_TIME KERNEL DEFAULT
STUDENT PRIVATE_SGA KERNEL DEFAULT
STUDENT FAILED_LOGIN_ATTEMPTS PASSWORD 3
STUDENT PASSWORD_LIFE_TIME PASSWORD 30
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
STUDENT PASSWORD_REUSE_TIME PASSWORD DEFAULT
STUDENT PASSWORD_REUSE_MAX PASSWORD DEFAULT
STUDENT PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
STUDENT PASSWORD_LOCK_TIME PASSWORD 5
STUDENT PASSWORD_GRACE_TIME PASSWORD DEFAULT
16 rows selected.
SQL>
SQL>
3.4 修改用户¶
#1.修改用户配置文件
create profile common limit
failed_login_attempts 5
idle_time 5;
alter user chupeng profile common;
#2.修改密码
alter user chupeng identified by '123456';
#3.连接测试
conn chupeng/123456
#4.创建表空间
mkdir /oradata
create tablespace test datafile '/oradata/test.dbf' size 20M;
#5.修改用户默认表空间
alter user chupeng default tablespace test;
#6.创建临时表空间
create temporary tablespace temp_data tempfile '/oradata/temp_data.dbf' size 20M;
#7.修改用户临时表空间
alter user chupeng temporary tablespace temp_data;
#8.强制用户修改密码
alter user chupeng password expire;
#9.用户加锁解锁(很少用)
alter user chupeng account lock;
alter user chupeng account unlock;
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 13:32:31 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create profile common limit
failed_login_attempts 5
idle_time 5; 2 3
Profile created.
SQL>
SQL> alter user chupeng profile common;
User altered.
SQL>
SQL> alter user chupeng identified by '123456';
alter user chupeng identified by '123456'
*
ERROR at line 1:
ORA-00988: missing or invalid password(s)
SQL> alter user chupeng identified by 'cpu123456';
alter user chupeng identified by 'cpu123456'
*
ERROR at line 1:
ORA-00988: missing or invalid password(s)
SQL> alter user chupeng identified by '321';
alter user chupeng identified by '321'
*
ERROR at line 1:
ORA-00988: missing or invalid password(s)
SQL>
#修改密码报错。
SQL> alter user chupeng identified by 123456;
User altered.
SQL>
#发现原来不能带引号。
SQL>
SQL> conn chupeng/123
ERROR:
ORA-01045: user CHUPENG lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
SQL>exit
[root@152 ~]# mkdir /oradata
[root@152 ~]# ls /oradata -dl
drwxr-xr-x 2 root root 6 May 20 13:36 /oradata
[root@152 ~]#
[root@152 ~]#
[root@152 ~]# chown -R dba.oracle /oradata/*
chown: invalid user: ‘dba.oracle’
[root@152 ~]# chgrp dba /oradata
[root@152 ~]#
[root@152 ~]# chown oracle /oradata
[root@152 ~]# chgrp -R dba /oradata
[root@152 ~]# chown -R oracle /oradata
[root@152 ~]# ls /oradata -dl
drwxr-xr-x 2 oracle dba 6 May 20 13:36 /oradata
[root@152 ~]#
[root@152 ~]#
[root@152 ~]# su - oracle
Last login: Thu May 20 11:05:01 CST 2021 on pts/1
-bash: ulimit: max user processes: cannot modify limit: Operation not permitted
-bash-4.2$
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 13:41:04 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create tablespace test datafile '/oradata/test.dbf' size 20M;
Tablespace created.
SQL>
SQL> alter user chupeng default tablespace test;
User altered.
SQL>
SQL> create temporary tablespace temp_data tempfile '/oradata/temp_data.dbf' size 20M;
Tablespace created.
SQL>
SQL> alter user chupeng temporary tablespace temp_data;
User altered.
SQL>
SQL> alter user chupeng password expire;
User altered.
SQL>
SQL> alter user chupeng account lock;
alter user chupeng account unlock;
User altered.
SQL>
User altered.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
3.5 删除用户¶
#1.只删除用户
drop user chupeng
#2.将用户及其所建实体全部删除
drop user chupeng cascade;
3.6 创建用户及授权管理权限¶
#1.创建用户
create user user50 identified by user50;
#2.授予权限
grant connect,resource to user50;
#3.在线切换用户
conn user50/user50
#4.测试
create tbale t1(id int,name varchar2(20));
insert into t1 values(1,'zs');
commit;
select * from t1;
select table_name from user_tables;
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 13:44:36 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL>
SQL> create user user50 identified by user50;
User created.
SQL>
SQL> grant connect,resource to user50;
Grant succeeded.
SQL>
SQL> conn user50/user50
Connected.
SQL> create table t1(id int,name varchar2(20));
Table created.
SQL>
SQL> insert into t1 values(1,'zs');
1 row created.
SQL>
SQL> select * from t1;
ID NAME
---------- --------------------
1 zs
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
T1
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
3.7 查询用户拥有哪些权限¶
set linesize 200;
#这一个就够了
select * from dba_role_privs where grantee='user50';
#这一个巡检时用到，必须保证普通用户的adm为no！
select * from dba_sys_privs where grantee='user50';
#这个貌似没有，后面老师删掉了
select * from role_sys_privs where grantee='user50';
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 13:48:10 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> set linesize 200;
SQL>
SQL> select * from dba_role_privs where grantee='user50';
no rows selected
SQL>
SQL> select * from dba_sys_privs where grantee='user50';
no rows selected
SQL>
SQL> select * from role_sys_privs where grantee='user50';
select * from role_sys_privs where grantee='user50'
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GRANTEE'
SQL>
#查不到。原来引号中必须要大写！！！
SQL> select * from dba_role_privs where grantee='USER50';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
USER50 RESOURCE NO YES
USER50 CONNECT NO YES
SQL>
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='USER50';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
USER50 UNLIMITED TABLESPACE NO
SQL>
#这一个依旧查不到
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE GRANTEE='USER50';
SELECT * FROM ROLE_SYS_PRIVS WHERE GRANTEE='USER50'
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GRANTEE'
SQL> EXIT
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
3.8 系统权限传递¶
#加上with admin option即可
grant connect,resource to user50 with admin option;
3.9 系统权限回收¶
#系统权限只能由dba用户回收！
revoke connect,resource from user50;
#普通对象权限管理
create user user01 identified by 123;
create user user02 identified by 123;
grant select,update,insert on t1 to user01;
grant all on t1 to user02;
#将表的操作权限授予全体用户(这个命令不安全，很少用)
grant all on t1 to public;
#用户可以查询的表
select owner,table_name from all_tables;
#用户创建的表
select table_name from user_tables;
#获权可以存取的表（被授权的表）
select grantor,table_schema,table_name,privilege from all_tab_privs;
#授出权限的表（授出的权限）
select grantee,owner,table_name,privilege from user_tab_privs;
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 14:09:51 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> revoke connect,resource from user50;
Revoke succeeded.
SQL>
SQL> create user user01 identified by 123;
User created.
SQL> create user user02 identified by 123;
User created.
SQL>
SQL> grant select ,update,insert on t1 to user01;
grant select ,update,insert on t1 to user01
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table t1(id int);
Table created.
SQL> grant select,update,insert on t1 to user01;
Grant succeeded.
SQL> grant all on t1 to user02;
Grant succeeded.
SQL>
SQL> grant all on t1 to public;
Grant succeeded.
SQL>
SQL> select owner,table_name from all_tables;
#剩余几条查询内容过多。不演示
4. oracle角色¶
4.1 系统预定义角色¶
#预定义角色，是在数据库安装后，系统自动创建的一些常用的角色。
connect，resource，dba
这些角色主要是为了向后兼容，其主要是用于数据库管理。
#2.查询角色所包含的权限
select * from role_sys_privs where role='角色名'；
SQL> select * from role_sys_privs where role='connect';
no rows selected
SQL>
SQL> select * from role_sys_privs where role='CONNECT';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CONNECT CREATE SESSION NO
SQL>
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
8 rows selected.
SQL>
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='DBA';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA CREATE SESSION YES
DBA ALTER SESSION YES
DBA DROP TABLESPACE YES
DBA BECOME USER YES
DBA DROP ROLLBACK SEGMENT YES
DBA SELECT ANY TABLE YES
DBA INSERT ANY TABLE YES
DBA UPDATE ANY TABLE YES
DBA DROP ANY INDEX YES
DBA SELECT ANY SEQUENCE YES
DBA CREATE ROLE YES
4.2 创建角色¶
create role role1;
4.3 授权给角色¶
grant create any table,create procedure to role1;
4.4 授予角色给用户¶
grant role1 to user1;
4.5 查看角色所包含的权限¶
select * from role_sys_privs;
4.6 创建带有口令的角色（在生效带有口令的角色时，必须提供口令！）(了解即可)¶
create role role1 identified by password1;
4.7 修改角色：是否需要口令（了解即可）¶
alter role role1 not identified;
alter role role1 identified by password1;
SQL> create role role1;
Role created.
SQL>
SQL> grant create any table,create procedure to role1;
Grant succeeded.
SQL>
SQL> grant role1 to user1;
grant role1 to user1
*
ERROR at line 1:
ORA-01917: user or role 'USER1' does not exist
SQL>
SQL>
SQL> create user user1 identified by 123;
User created.
SQL>
SQL> grant role1 to user1;
Grant succeeded.
SQL>
SQL> select * from role_sys_privs where grantee='ROLE1';
select * from role_sys_privs where grantee='ROLE1'
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GRANTEE'
SQL> select * from role_sys_privs where ROLE='ROLE1';
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
ROLE1 CREATE PROCEDURE NO
ROLE1 CREATE ANY TABLE NO
SQL>
4.8 设置当前用户要生效的角色（了解）¶
#注：角色的生效是什么概念呢？
#假设用户a有b1，b2，b3三个角色
#如果b1未生效，则b1所包含的权限对于a来讲，是不拥有的。只有角色生效了，角色内的权限才能作用于用户，最大可生效的角色数是由参数max_enabled_roles来设定
#在用户登录后，oracle将所有直接赋给用户的权限和用户默认角色中的权限赋给用户
set role role1;
set role role1,role2;
set role role identified by password1;
set role all;
set role none;
set role all except role1;
#查看当前用户的生效的角色
select * from session_roles;
4.9 删除角色¶
drop role role1;
4.10 需要记住的3个命令¶
#用户，权限，角色的概念，面试经常会问
create user user1 identified by 123;
grant connect,resource to user1;
grant connect,resource,dba to user2;
5. oracle物理及逻辑结构¶
一个用户就是一个schema
一个schema下有多个对象（表、存储过程等）
用户要存对象时，使用表空间来进行存储
从物理角度来讲，tbs由多个数据文件构成
从逻辑角度来讲，对象存储，使用的段进行存储
往表里存储数据时，分配多个连续的数据块，我们把它称之为区
默认一个区1M大小，也就是连续的128个block大小（默认的block数据块是8k）
我们的数据文件，可以定义到多种存储结构上
存储结构：
SAN,NAS,NFS,Exadata
数据管理方式：
FS
RAW
ASM(11g推荐)
现在最流行的是，基于SAN存储结构的ASM管理方式
6. 参数文件¶
6.1 种类¶
spfile:
动态的，二进制的参数文件，在线参数文件
pfile:
静态的，文本类型的参数文件，离线的，一般做维护时使用
命名方式：
init<sid>.ora
6.2 优先级¶
spfile<sid>.ora ----> spfile.ora ----> init<sid>.ora
6.3 默认目录¶
cd $ORACLE_HOME/dbs
-bash-4.2$ cd $ORACLE_HOME/dbs
-bash-4.2$ ls
hc_XE.dat init.ora lkXE orapwXE spfileXE.ora
-bash-4.2$ pwd
/u01/app/oracle/product/11.2.0/xe/dbs
6.4 参数文件的转换¶
#查看spfile目录
show parameters spfile
#spfile >> pfile
create pfile from spfile; #创建到默认目录
create pfile='/tmp/aa.txt' from spfile; #创建到指定目录
!ls $ORACLE_HOME/dbs/
#生效在指定路径
create pfile='/tmp/initxe.ora' from spfile;
#pfile >> spfile
create spfile='/tmp/spfilexe.ora' from pfile;
#修改pfile配置后，重新配置到spfile
sqlplus / as sysdba
shutdown immediate
create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/aa.txt';
startup
#以pfile启动
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 14:50:50 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> show parameters spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/xe/dbs/spfileXE.ora
SQL>
SQL> !ls $ORACLE_HOME/dbs
hc_XE.dat init.ora lkXE orapwXE spfileXE.ora
SQL>
SQL> create pfile from spfile;
File created.
SQL>
SQL> !ls $ORACLE_HOME/dbs
hc_XE.dat init.ora initXE.ora lkXE orapwXE spfileXE.ora
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ cd $ORACLE_HOME/dbs
-bash-4.2$
-bash-4.2$ ls
hc_XE.dat init.ora initXE.ora lkXE orapwXE spfileXE.ora
-bash-4.2$
-bash-4.2$ cat initXE.ora
XE.__db_cache_size=130023424
XE.__java_pool_size=4194304
XE.__large_pool_size=4194304
XE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
XE.__pga_aggregate_target=142606336
XE.__sga_target=264241152
XE.__shared_io_pool_size=0
XE.__shared_pool_size=117440512
XE.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/XE/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/XE/control.dbf'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.log_archive_dest_1='location=/u01/arch/'
*.memory_target=406847488
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
-bash-4.2$
#创建pfile到指定目录
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 14:54:18 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> create pfile='/tmp/aa.txt' from spfile;
File created.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$ cd /tmp
-bash-4.2$ ls
aa.txt vmware-root_5462-2856518146
-bash-4.2$
-bash-4.2$ cat aa.txt
XE.__db_cache_size=130023424
XE.__java_pool_size=4194304
XE.__large_pool_size=4194304
XE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
XE.__pga_aggregate_target=142606336
XE.__sga_target=264241152
XE.__shared_io_pool_size=0
XE.__shared_pool_size=117440512
XE.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/XE/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/XE/control.dbf'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.log_archive_dest_1='location=/u01/arch/'
*.memory_target=406847488
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 15:01:08 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/aa.txt';
File created.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 260048112 bytes
Database Buffers 138412032 bytes
Redo Buffers 4333568 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameters spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/xe/dbs/spfileXE.ora
SQL>
SQL>
6.5 参数文件的作用¶
#影响oracle数据库的启动
6.6 参数文件详解¶
-bash-4.2$ cat /tmp/aa.txt
#审计日志
*.audit_file_dest='/u01/app/oracle/admin/XE/adump'
#版本
*.compatible='11.2.0.0.0'
#控制文件（重点）
*.control_files='/u01/app/oracle/oradata/XE/control.dbf'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
#诊断日志
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
#归档日志
*.log_archive_dest_1='location=/u01/arch/'
#总内存大小设置
*.memory_target=406847488
#打开游标的数量（貌似是索引使用游标）
*.open_cursors=300
#远程用户登录使用的密码文件
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
#undo表空间的位置
*.undo_tablespace='UNDOTBS1'
6.7 判断数据库是从spfile还是从pfile启动¶
#方式1
show parameter spfile
#如果有值，则是从spfile启动
#方式2
set linesize 120
col name format a10
col value format a60
select name,value from v$parameter where name='spfile';
#以pfile启动
startup pfile='/tmp/aa.txt';
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 15:12:14 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/xe/dbs/spfileXE.ora
SQL>
SQL> set linesize 120
col name format a10
col value format a60SQL> SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select name,value from v$parameter where name='spfile';
NAME VALUE
---------- ------------------------------------------------------------
spfile /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup pfile='/tmp/aa.txt';
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 260048112 bytes
Database Buffers 138412032 bytes
Redo Buffers 4333568 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL>
SQL> select name,value from v$parameter where name='spfile';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
spfile
SQL>
6.8 修改参数文件的三种模式¶
scope=both #立即并永久生效（默认）
scope=spfile #下次启动生效
scope=memory #立即生效但下次启动失效
#查询方法
select name,isses_modifiable,issys_modifiable from v$parameter where name='sga_max_size';
#isses_modifiable
true:表明这个参数可以在session级别修改并且立即生效
false：表示这个参数不能在内存中直接修改，需要加scope=spfile，重启后才能生效
immediate：表示这个参数可以在system立即修改，并且立即生效
deferred：会话级别参数
#对于即时生效的参数
alter system set xx=xx;
#对于静态参数
alter system set xx=xx scope=spfile;
#例
#修改静态参数
alter system set sga_max_size=900M;
alter system set sga_max_size=800M scope=spfile;
#恢复参数到默认值
alter system reset sga_max_size scope=spfile;
SQL> select name,isses_modifiable,issys_modifiable from v$parameter where name='sga_max_size';
NAME ISSES ISSYS_MOD
---------- ----- ---------
sga_max_si FALSE FALSE
ze
SQL>
SQL> alter system set sga_max_size=900M;
alter system set sga_max_size=900M
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
SQL> alter system set sga_max_size=800M scope=spfile;
System altered.
SQL>
SQL> alter system reset sga_max_size scope=spfile;
System altered.
SQL>
SQL>
7. 控制文件¶
7.1 控制文件存储的主要信息¶
数据库名称和sid标识
数据文件和日志文件列表
数据库创建的时间戳
表空间信息
当前重做日志文件序列号
归档日志信息
检查点信息
回滚段的起始与结束
备份数据文件信息
7.2 控制文件查看¶
select name from v$controlfile;
show parameter control_files;
SQL> select name from v$controlfile;
NAME
----------
/u01/app/o
racle/orad
ata/XE/con
trol.dbf
SQL>
SQL> show parameter control files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/XE/con
trol.dbf
control_management_pack_access string NONE
SQL>
SQL>
7.3 添加控制文件¶
#为了提高数据库的安全性，至少要为数据库建立2个控制文件，而且这2个文件最好分别放在不同的磁盘中，这样可以避免产生由于某个磁盘故障而无法启动数据库的危险。该管理策略成为多路复用控制文件
#当多路复用控制文件某个磁盘发生故障导致其包含的控制文件损坏，数据库将被关闭或者发生异常，此时可以用另一个磁盘中保存的控制文件来恢复被损坏的控制位文件，然后再重启数据库，达到保护控制文件的目的。
show parameter control;
shutdown immediate;
cd /u01/app/oracle/oradata/XE
cp /u01/app/oracle/oradata/XE/control.dbf /oradata/control01.dbf
cp /u01/app/oracle/oradata/XE/control.dbf /oradata/control02.dbf
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 15:41:44 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/XE/con
trol.dbf
SQL>
SQL>
SQL>
SQL>
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/XE/con
trol.dbf
control_management_pack_access string NONE
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ cd /u01/app/oracle/oradata/XE
-bash-4.2$
-bash-4.2$ ls
control.dbf sysaux.dbf system.dbf temp.dbf undotbs1.dbf users.dbf
-bash-4.2$
-bash-4.2$ cp /u01/app/oracle/oradata/XE/control.dbf /oradata/control01.dbf
-bash-4.2$ cp /u01/app/oracle/oradata/XE/control.dbf /oradata/control02.dbf
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls /oradata/
control01.dbf control02.dbf temp_data.dbf test.dbf
-bash-4.2$
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 15:45:30 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> create pfile='/tmp/init.ora' from spfile;
File created.
SQL>
SQL> !vim /tmp/init.ora
SQL>
SQL> !cat /tmp/init.ora
XE.__db_cache_size=130023424
XE.__java_pool_size=4194304
XE.__large_pool_size=4194304
XE.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
XE.__pga_aggregate_target=163577856
XE.__sga_target=243269632
XE.__shared_io_pool_size=0
XE.__shared_pool_size=96468992
XE.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/XE/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/XE/control.dbf','/oradata/control01.dbf','/oradata/control02.dbf' #修改此行
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.log_archive_dest_1='location=/u01/arch/'
*.memory_target=406847488
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 268436720 bytes
Database Buffers 130023424 bytes
Redo Buffers 4333568 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/XE/con
trol.dbf
control_management_pack_access string NONE
SQL>
SQL>
#发现没有出现预期结果！
#忘记把pfile转换回去了，再次测试，发现启动不了了。
SQL>
SQL> create spfile from pfile='/tmp/init.ora';
File created.
SQL>
SQL> show parameter control;
ORA-01034: ORACLE not available
Process ID: 65477
Session ID: 1 Serial number: 5
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 268436720 bytes
Database Buffers 130023424 bytes
Redo Buffers 4333568 bytes
ORA-00214: control file '/u01/app/oracle/oradata/XE/control.dbf' version 728
inconsistent with file '/oradata/control01.dbf' version 714
SQL>
#虽然报错了，但是依旧发现控制文件加上了！
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/XE/con
trol.dbf, /oradata/control01.d
bf, /oradata/control02.dbf
control_management_pack_access string NONE
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ ps -ef|grep smon
oracle 70373 1 0 16:07 ? 00:00:00 xe_smon_XE
oracle 73322 17519 0 16:15 pts/1 00:00:00 grep --color=auto smon
-bash-4.2$
7.4 模拟1个控制文件损坏¶
#1.模拟控制1个文件损坏
rm -rf /oradata/control01.dbf
#2.关库测试
shutdown immediate
#发现关闭不了了！
#3.强制关闭数据库
shutdown abort
#4.尝试启动数据库
startup
#5.查看日志文件
cd /u01/app/oracle/diag/rdbms/xe/XE/trace/
ls -l alert_*
tail -20 alert_XE.log
#6.复制一个控制文件，或者从配置文件中减少一个
cp /oradata/control02.dbf /oradata/control01.dbf
#7.再次启动
startup
#8.查看运行状态
select open_mode from v$database;
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 16:19:45 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> !ls /oradata
control01.dbf control02.dbf temp_data.dbf test.dbf
SQL>
SQL> !rm -rf /oradata/control01.dbf
SQL>
SQL> !ls /oradata
control02.dbf temp_data.dbf test.dbf
SQL>
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ ps -ef|grep smon
oracle 75358 17519 0 16:20 pts/1 00:00:00 grep --color=auto smon
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 16:21:06 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> show parameter control
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 268436720 bytes
Database Buffers 130023424 bytes
Redo Buffers 4333568 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ cd /u01/app/oracle/diag/rdbms/xe/XE/trace/
-bash-4.2$
-bash-4.2$ ls -l alert_*
-rwxrwxr-x 1 oracle dba 58707 May 20 16:22 alert_XE.log
-bash-4.2$
-bash-4.2$ tail -20 alert_XE.log
SMON started with pid=13, OS id=75840
Thu May 20 16:22:08 2021
RECO started with pid=14, OS id=75843
Thu May 20 16:22:08 2021
MMON started with pid=15, OS id=75845
Thu May 20 16:22:08 2021
MMNL started with pid=16, OS id=75847
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 4 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Thu May 20 16:22:08 2021
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/control01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Thu May 20 16:22:08 2021
Checker run found 1 new persistent data failures
-bash-4.2$
#根据日志发现，是控制文件丢失
-bash-4.2$ cd /oradata/
-bash-4.2$ ls
control02.dbf temp_data.dbf test.dbf
-bash-4.2$
-bash-4.2$ cp control02.dbf control01.dbf
-bash-4.2$
-bash-4.2$ ls
control01.dbf control02.dbf temp_data.dbf test.dbf
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 16:26:13 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 16:26:27 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/XE/con
trol.dbf, /oradata/control01.d
bf, /oradata/control02.dbf
control_management_pack_access string NONE
SQL>
SQL>
#复制了后，自动启动了？？？
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL>
#发现不可用！！！如何解决？
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 80310
Session ID: 1 Serial number: 5
SQL>
SQL> create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/init.ora';
File created.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 268436720 bytes
Database Buffers 130023424 bytes
Redo Buffers 4333568 bytes
ORA-00214: control file '/u01/app/oracle/oradata/XE/control.dbf' version 728
inconsistent with file '/oradata/control01.dbf' version 714
SQL>
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL>
#依旧不行。删除配置文件中的其他控制文件，再次启动测试
SQL> !cat /tmp/aa.txt
*.audit_file_dest='/u01/app/oracle/admin/XE/adump'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/XE/control.dbf'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.log_archive_dest_1='location=/u01/arch/'
*.memory_target=406847488
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
SQL>
SQL> create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/aa.txt';
create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/aa.txt'
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance
SQL>
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL>
SQL> create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/aa.txt';
File created.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 260048112 bytes
Database Buffers 138412032 bytes
Redo Buffers 4333568 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
7.5 查看数据文件和日志文件目录¶
#数据文件路径
desc dba_data_files;
select file_name from dba_data_files;
#日志文件路径
desc v$logfile;
set linesize 200
select group#,member from v$logfile;
#有了这些，就可以创建控制文件了
SQL>
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/XE/users.dbf
/u01/app/oracle/oradata/XE/sysaux.dbf
/u01/app/oracle/oradata/XE/undotbs1.dbf
/u01/app/oracle/oradata/XE/system.dbf
/oradata/test.dbf
SQL>
SQL>
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL>
SQL> set linesize 200
SQL>
SQL> select group#,member from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log
1
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log
SQL>
7.6 备份控制文件¶
#备份控制文件
alter database backup controlfile to trace;
7.7 查看trace路径¶
#查看trace路径
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 16:47:44 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_85476.trc
SQL>
SQL>
SQL>
SQL>
SQL> alter database backup controlfile to trace;
Database altered.
SQL>
SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_85476.trc
SQL>
7.8 查看trace内容¶
cat /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_85476.trc
-bash-4.2$ cat /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_85476.trc
Trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_85476.trc
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe
System name: Linux
Node name: 152
Release: 3.10.0-957.el7.x86_64
Version: #1 SMP Thu Nov 8 23:39:32 UTC 2018
Machine: x86_64
VM name: VMWare Version: 6
Instance name: XE
Redo thread mounted by this instance: 1
Oracle process number: 31
Unix process pid: 85476, image: oracle@152 (TNS V1-V3)
*** 2021-05-20 16:48:11.904
*** SESSION ID:(35.11) 2021-05-20 16:48:11.904
*** CLIENT ID:() 2021-05-20 16:48:11.904
*** SERVICE NAME:(SYS$USERS) 2021-05-20 16:48:11.904
*** MODULE NAME:(sqlplus@152 (TNS V1-V3)) 2021-05-20 16:48:11.904
*** ACTION NAME:() 2021-05-20 16:48:11.904
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="XE"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch/'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/XE/system.dbf',
'/u01/app/oracle/oradata/XE/sysaux.dbf',
'/u01/app/oracle/oradata/XE/undotbs1.dbf',
'/u01/app/oracle/oradata/XE/users.dbf',
'/oradata/test.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/1_1_760399845.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/1_1_1072775924.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/XE/temp.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP_DATA ADD TEMPFILE '/oradata/temp_data.dbf'
SIZE 20971520 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/XE/system.dbf',
'/u01/app/oracle/oradata/XE/sysaux.dbf',
'/u01/app/oracle/oradata/XE/undotbs1.dbf',
'/u01/app/oracle/oradata/XE/users.dbf',
'/oradata/test.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/1_1_760399845.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/arch/1_1_1072775924.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/XE/temp.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP_DATA ADD TEMPFILE '/oradata/temp_data.dbf'
SIZE 20971520 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
-bash-4.2$
-bash-4.2$
-bash-4.2$
7.9 从上一步得到创建控制文件语句¶
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/XE/system.dbf',
'/u01/app/oracle/oradata/XE/sysaux.dbf',
'/u01/app/oracle/oradata/XE/undotbs1.dbf',
'/u01/app/oracle/oradata/XE/users.dbf',
'/oradata/test.dbf'
CHARACTER SET AL32UTF8
;
7.10 删除原来的控制文件¶
mv /u01/app/oracle/oradata/XE/control.dbf /tmp
mv /oradata/control01.dbf /tmp
mv /oradata/control02.dbf /tmp
7.11 重建控制文件¶
shutdown immediate
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "XE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/XE/system.dbf',
'/u01/app/oracle/oradata/XE/sysaux.dbf',
'/u01/app/oracle/oradata/XE/undotbs1.dbf',
'/u01/app/oracle/oradata/XE/users.dbf',
'/oradata/test.dbf'
CHARACTER SET AL32UTF8
;
alter database open resetlogs;
select open_mode from v$database;
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 16:58:44 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2226960 bytes
Variable Size 268436720 bytes
Database Buffers 130023424 bytes
Redo Buffers 4333568 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
-bash-4.2$
-bash-4.2$ mv /u01/app/oracle/oradata/XE/control.dbf /tmp
-bash-4.2$ mv /oradata/control01.dbf /tmp
-bash-4.2$ mv /oradata/control02.dbf /tmp
-bash-4.2$
-bash-4.2$ ls /u01/app/oracle/oradata/XE/
sysaux.dbf system.dbf temp.dbf undotbs1.dbf users.dbf
-bash-4.2$
-bash-4.2$ ls /oradata
temp_data.dbf test.dbf
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 20 17:00:13 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "XE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/XE/system.dbf',
'/u01/app/oracle/oradata/XE/sysaux.dbf',
'/u01/app/oracle/oradata/XE/undotbs1.dbf',
'/u01/app/oracle/oradata/XE/users.dbf',
'/oradata/test.dbf'
CHARACTER SET AL32UTF8
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Control file created.
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
SQL>
8. 表空间¶
8.1 表空间介绍¶
8.2 永久表空间创建¶
#查看表空间
desc v$tablespace;
select name from v$tablespace;
#select tablespace_name,status from dba_tablespaces; #xe不存在这个表 ！！！
desc dba_data_files;
select tablespace_name,file_name from dba_data_files;
#创建50MB的永久表空间test01，禁止自动扩展数据文件
create tablespace test01
logging
datafile '/oradata/test01.dbf' size 50M
reuse autoextend off;
#创建永久表空间test02，允许自动扩展数据文件，本地管理方式(常用)
create tablespace test02
logging
datafile '/oradata/test02.dbf' size 50M
reuse autoextend on next 10M maxsize 200M
extent management local;
#创建永久表空间test03，允许自动扩展数据文件，本地管理方式，区分配方式为自动分配
create tablespace test03
logging
datafile '/oradata/test03.dbf' size 50M
reuse autoextend on next 10M maxsize 200M
extent management local autoallocate; #系统自动管理
#创建永久表空间test04，允许自动扩展数据文件，本地管理方式，区分配方式为定制分配(重点)
create tablespace test04
logging
datafile '/oradata/test04.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local uniform size 10m;
#创建永久表空间test05，允许自动扩展数据文件，本地管理方式，区分配方式为自动分配，段管理方式为自动管理
create tablespace test05
logging
datafile '/oradata/test05.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local autoallocate
segment space management auto;
#创建永久表空间test06，允许自动扩展数据文件，本地管理方式，区分配方式为定制分配，段管理方式为手动管理
create tablespace test06
logging
datafile '/oradata/test06.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local uniform size 10m
segment space management manual;
#再次查看
select name from v$tablespace;
select tablespace_name,file_name from dba_data_files;
[root@152 ~]# su - oracle
Last login: Thu May 20 13:40:37 CST 2021 on pts/1
-bash: ulimit: max user processes: cannot modify limit: Operation not permitted
-bash-4.2$
-bash-4.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri May 21 08:44:01 2021
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>
SQL> desc v$tablespace
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
SQL>
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEST
TEMP
TEMP_DATA
7 rows selected.
SQL>
SQL> desc dba_tablespace;
ERROR:
ORA-04043: object dba_tablespace does not exist
SQL> desc dab_tablespaces;
ERROR:
ORA-04043: object dab_tablespaces does not exist
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL>
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEST
/oradata/test.dbf
USERS
/u01/app/oracle/oradata/XE/users.dbf
UNDOTBS1
/u01/app/oracle/oradata/XE/undotbs1.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSAUX
/u01/app/oracle/oradata/XE/sysaux.dbf
SYSTEM
/u01/app/oracle/oradata/XE/system.dbf
SQL>
SQL> set linesize 200
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST
/oradata/test.dbf
USERS
/u01/app/oracle/oradata/XE/users.dbf
UNDOTBS1
/u01/app/oracle/oradata/XE/undotbs1.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSAUX
/u01/app/oracle/oradata/XE/sysaux.dbf
SYSTEM
/u01/app/oracle/oradata/XE/system.dbf
SQL>
SQL> crreate tablespace test01
logging
datafile '/oradata/test01.dbf' size 50M
reuse autoextend off;SP2-0734: unknown command beginning "crreate ta..." - rest of line ignored.
SQL> SP2-0042: unknown command "logging" - rest of line ignored.
SQL> SP2-0734: unknown command beginning "datafile '..." - rest of line ignored.
SQL>
SP2-0734: unknown command beginning "reuse auto..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
#此处报错，然后尝试另一种格式
SQL> crreate tablespace test01 logging datafile '/oradata/test01.dbf' size 50M reuse autoextend off;
SP2-0734: unknown command beginning "crreate ta..." - rest of line ignored.
#发现原来是因为单词拼错了。。。
SQL>
SQL> create tablespace test01
logging
datafile '/oradata/test01.dbf' size 50M
reuse autoextend off; 2 3 4
Tablespace created.
SQL>
SQL>
SQL> create tablespace test02
logging
datafile '/oradata/test02.dbf' size 50M
reuse autoextend on next 10M maxsize 200M
extent management local; 2 3 4 5
Tablespace created.
SQL>
SQL>
SQL> create tablespace test03
logging
datafile '/oradata/test03.dbf' size 50M
reuse autoextend on next 10M maxsize 200M
extent management local autoallocate; 2 3 4 5
Tablespace created.
SQL>
SQL>
SQL> create tablespace test04
logging
datafile '/oradata/test04.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local uniform size 10m; 2 3 4 5
Tablespace created.
SQL>
SQL>
SQL> create tablespace test05
logging
datafile '/oradata/test05.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local autoallocate
segment space management auto; 2 3 4 5 6
Tablespace created.
SQL>
SQL>
SQL> create tablespace test06
logging
datafile '/oradata/test06.dbf' size 50m
reuse autoextend on next 10m maxsize 200m
extent management local uniform size 10m
segment space management manual; 2 3 4 5 6
Tablespace created.
SQL>
SQL>
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEST
TEMP
TEMP_DATA
TEST01
TEST02
TEST03
TEST04
NAME
------------------------------
TEST05
TEST06
13 rows selected.
SQL>
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST
/oradata/test.dbf
USERS
/u01/app/oracle/oradata/XE/users.dbf
UNDOTBS1
/u01/app/oracle/oradata/XE/undotbs1.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSAUX
/u01/app/oracle/oradata/XE/sysaux.dbf
SYSTEM
/u01/app/oracle/oradata/XE/system.dbf
TEST01
/oradata/test01.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST02
/oradata/test02.dbf
TEST03
/oradata/test03.dbf
TEST04
/oradata/test04.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST05
/oradata/test05.dbf
TEST06
/oradata/test06.dbf
11 rows selected.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.2$
8.3 临时表空间创建¶
#创建一个临时表空间test07，为了避免临时空间频繁分配与回收时产生大量碎片，临时表空间的区只能采用自动分配方式
create temporary tablespace test07
tempfile '/oradata/test07.dbf' size 20m;
#查看
select name from v$tablespace;
SQL> create temporary tablespace test07
tempfile '/oradata/test07.dbf' size 20m; 2
Tablespace created.
SQL>
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEST
TEMP
TEMP_DATA
TEST01
TEST02
TEST03
TEST04
NAME
------------------------------
TEST05
TEST06
TEST07
14 rows selected.
SQL>
#此命令看不到
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEST
/oradata/test.dbf
USERS
/u01/app/oracle/oradata/XE/users.dbf
UNDOTBS1
/u01/app/oracle/oradata/XE/undotbs1.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSAUX
/u01/app/oracle/oradata/XE/sysaux.dbf
SYSTEM
/u01/app/oracle/oradata/XE/system.dbf
TEST01
/oradata/test01.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEST02
/oradata/test02.dbf
TEST03
/oradata/test03.dbf
TEST04
/oradata/test04.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEST05
/oradata/test05.dbf
TEST06
/oradata/test06.dbf
11 rows selected.
SQL>
8.4 undo表空间创建¶
#创建一个undo表空间test08
create undo tablespace test08
datafile '/oradata/test08.dbf' size 20m;
SQL> create undo tablespace test08
datafile '/oradata/test08.dbf' size 20m; 2
Tablespace created.
SQL>
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEST
TEMP
TEMP_DATA
TEST01
TEST02
TEST03
TEST04
NAME
------------------------------
TEST05
TEST06
TEST07
TEST08
15 rows selected.
SQL>
8.5 表空间管理¶
#换存储，换设备，这是将来dba经常做的，以下就是经典的换存储案例！
#通过 alter tablespace 命令，修改表空间中数据文件的位置或名字
#1.首先把该表空间设置为脱机状态
select tablespace_name,status from dba_tablespaces;
alter tablespace test03 offline normal;
select tablespace_name,status from dba_tablespaces;
#2.手动创建test03文件夹，并且复制test03.dbf到该文件夹，重命名为test03_1.dbf
!mkdir /oradata/test03
!cp /oradata/test03.dbf /oradata/test03/test03_1.dbf
alter tablespace test03 rename datafile
'/oradata/test03.dbf'
to
'/oradata/test03/test03_1.dbf';
#3.最后把该表空间设置为联机状态
alter tablespace test03 online;
#4.查看
select tablespace_name,file_name from dba_data_files where tablespace_name = 'TEST03';
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST ONLINE
TEMP_DATA ONLINE
TEST01 ONLINE
TEST02 ONLINE
TEST03 ONLINE
TEST04 ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST05 ONLINE
TEST06 ONLINE
TEST07 ONLINE
TEST08 ONLINE
15 rows selected.
SQL>
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TEST03';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST03 ONLINE
SQL>
SQL> alter tablespace test03 offline normal;
Tablespace altered.
SQL>
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TEST03';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST03 OFFLINE
SQL>
SQL> !mkdir /oradata/test03
SQL>
SQL> !ls /oradata
temp_data.dbf test01.dbf test02.dbf test03 test03.dbf test04.dbf test05.dbf test06.dbf test07.dbf test08.dbf test.dbf
SQL>
SQL> alter tablespace test03 rename datafile
'/oradata/test03.dbf'
to
'/oradata/test03/test03_1.dbf'; 2 3 4
alter tablespace test03 rename datafile
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01141: error renaming data file 8 - new file '/oradata/test03/test03_1.dbf'
not found
ORA-01110: data file 8: '/oradata/test03.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> !cp /oradata/test03.dbf /oradata/test03/test03_1.dbf
SQL>
SQL> !ls /oradata/test03
test03_1.dbf
SQL>
SQL> alter tablespace test03 rename datafile
'/oradata/test03.dbf'
to
'/oradata/test03/test03_1.dbf'; 2 3 4
Tablespace altered.
SQL>
SQL> alter tablespace test03 online;
Tablespace altered.
SQL>
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name = 'TEST03';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST03 ONLINE
SQL>
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'TEST03';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEST03
/oradata/test03/test03_1.dbf
SQL>
8.6 删除表空间(谨慎！)¶
#删除表空间及其对应的数据文件
drop tablespace test01 including contents and datafiles;
8.7 查询表空间使用情况¶
#dba巡检天天要做的！！！
#查询表空间的使用情况
select tablespace_name,count(*) as extends,round(sum(bytes) / 1024 /1024, 2) as MB,sum(blocks) as blocks from dba_free_space group by tablespace_name;
#查询表空间的总容量
select tablespace_name,sum(bytes) /1024 / 1024 as MB from dba_data_files group by tablespace_name;
#查看表空间的使用率
select total.tablespace_name,
round(total.MB,2) as Total_MB,
round(total.MB-free.MB,2) as Used_MB,
round((1-free.MB/total.MB)*100,2)
|| '%' as Used_Pct
from (select tablespace_name,
sum(bytes)/1024/1024 as MB
from dba_free_space
group by tablespace_name)free,
(select tablespace_name,
sum(bytes)/1024/1024 as MB
from dba_data_files
group by tablespace_name)total
where free.tablespace_name=total.tablespace_name;
SQL> select tablespace_name,count(*) as extends,round(sum(bytes) / 1024 /1024, 2) as MB,sum(blocks) as blocks from dba_free_space group by tablespace_name;
TABLESPACE_NAME EXTENDS MB BLOCKS
------------------------------ ---------- ---------- ----------
TEST03 1 49 6272
SYSAUX 1 31.56 4040
UNDOTBS1 18 24.56 3144
TEST04 1 40 5120
TEST05 1 49 6272
USERS 1 97.44 12472
TEST 1 19 2432
TEST06 1 40 5120
TEST08 1 17.75 2272
SYSTEM 1 6.94 888
TEST02 1 49 6272
TABLESPACE_NAME EXTENDS MB BLOCKS
------------------------------ ---------- ---------- ----------
TEST01 1 49 6272
12 rows selected.
SQL>
SQL>
SQL> select tablespace_name,sum(bytes) /1024 / 1024 as MB from dba_data_files group by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
TEST03 50
UNDOTBS1 30
SYSAUX 640
TEST04 50
TEST05 50
TEST 20
USERS 100
TEST06 50
TEST08 20
SYSTEM 360
TEST02 50
TABLESPACE_NAME MB
------------------------------ ----------
TEST01 50
12 rows selected.
SQL>
SQL> select total.tablespace_name,
round(total.MB,2) as Total_MB,
round(total.MB-free.MB,2) as Used_MB,
round((1-free.MB/total.MB)*100,2)
|| '%' as Used_Pct
from (select tablespace_name,
sum(bytes)/1024/1024 as MB
from dba_free_space
group by tablespace_name)free,
(select tablespace_name,
sum(bytes)/1024/1024 as MB
from dba_data_files
group by tablespace_name)total
where free.tablespace_name=total.tablespace_name; 2 3 4 5 6 7 8 9 10 11 12 13 14
TABLESPACE_NAME TOTAL_MB USED_MB
------------------------------ ---------- ----------
USED_PCT
-----------------------------------------
TEST03 50 1
2%
SYSAUX 640 608.44
95.07%
UNDOTBS1 30 5.44
18.13%
TABLESPACE_NAME TOTAL_MB USED_MB
------------------------------ ---------- ----------
USED_PCT
-----------------------------------------
TEST04 50 10
20%
TEST05 50 1
2%
USERS 100 2.56
2.56%
TABLESPACE_NAME TOTAL_MB USED_MB
------------------------------ ---------- ----------
USED_PCT
-----------------------------------------
TEST 20 1
5%
TEST06 50 10
20%
TEST08 20 2.25
11.25%
TABLESPACE_NAME TOTAL_MB USED_MB
------------------------------ ---------- ----------
USED_PCT
-----------------------------------------
SYSTEM 360 353.06
98.07%
TEST02 50 1
2%
TEST01 50 1
2%
12 rows selected.
SQL>
8.8 表空间扩容¶
#1.表空间增加数据文件（最常用）
alter tablespace test01 add datafile '/oradata/test01_2.dbf' size 30M;
#2.表空间增加数据文件，设置自增长，限制最大值（最常用）
alter tablespace test01 add datafile '/oradata/test01_3.dbf' size 50M autoextend on maxsize 3072M;
#查询
select tablespace_name,file_name from dba_data_files where tablespace_name = 'TEST01';
#3.给已存在的表空间数据文件，设置自增长（了解）
alter database datafile '/oradata/test01.dbf' autoextend on maxsize 3072M;
#4.已存在的表空间文件，重新设置大小（了解）
alter database datafile '/oradata/test01_3.dbf' resize 80M;
#这两个如何查询？？？如何查询是否是自增长，如何查询表空间文件大小？？？
SQL> alter tablespace test01 add datafile '/oradata/test01_2.dbf' size 30M;
Tablespace altered.
SQL>
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'TEST01';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEST01
/oradata/test01.dbf
TEST01
/oradata/test01_2.dbf
SQL>
SQL> alter tablespace test01 add datafile '/oradata/test01_3.dbf' size 50M autoextend on maxsize 3072M;
Tablespace altered.
SQL>
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'TEST01';
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEST01
/oradata/test01.dbf
TEST01
/oradata/test01_2.dbf
TEST01
/oradata/test01_3.dbf
SQL>
SQL>
SQL> alter database datafile '/oradata/test01.dbf' autoextend on maxsize 3072M;
Database altered.
SQL>
SQL> alter database datafile '/oradata/test01_3.dbf' resize 80M;
Database altered.
SQL>
9. oracle数据库各个阶段转换¶
#数据库重要文件
#1.参数文件：
spfile
pfile
记录了controlfile的位置
数据库开启到nomount阶段
#2.控制文件：
controlfile
主要记录了数据文件和日志文件的位置、备份信息、scn信息
数据库可以开启到mount阶段
#3.数据文件：
系统相关：system sysaux undotbs1
临时：temp
用户表空间：users
数据库可以开启到open阶段
#4.各个阶段转换
shutdown immediate
startup nomount #nomount阶段
alter database mount; #mount阶段
alter database open; #open阶段
10. redo日志¶
10.1 redo日志状态查看¶
#1.查看状态
set linesize 200
select * from v$log;
#2.查看路径
col member for a30
select * from v$logfile;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 NO
CURRENT 386632 20-MAY-21 2.8147E+14
2 1 0 52428800 512 1 YES
UNUSED 0 0
SQL>
SQL>
SQL> set linesize 200
SQL> /
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 NO CURRENT 386632 20-MAY-21 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 0 0
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IS_
---
2 ONLINE
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log
YES
1 ONLINE
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log
YES
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IS_
---
SQL>
SQL>
SQL>
SQL> set linesize 200
SQL> /
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IS_
---
2 ONLINE
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_2_jb3k3nrp_.log
YES
1 ONLINE
/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log
YES
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IS_
---
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> col member for a30
SQL> /
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
2 ONLINE /u01/app/oracle/fast_recovery_ YES
area/XE/onlinelog/o1_mf_2_jb3k
3nrp_.log
1 ONLINE /u01/app/oracle/fast_recovery_ YES
area/XE/onlinelog/o1_mf_1_jb3k
3nkl_.log
SQL>
10.2 添加日志组¶
#1.目录必须存在
!mkdir /oradata/orcl
#2.添加日志组3
alter database add logfile group 3('/oradata/orcl/redo03a.log','/oradata/orcl/redo03b.log') size 20M;
#3.查看
select * from v$logfile;
SQL> alter database add logfile group 3('/oradata/orcl/redo03a.log','/oradata/orcl/redo03b.log') size 20M;
alter database add logfile group 3('/oradata/orcl/redo03a.log','/oradata/orcl/redo03b.log') size 20M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oradata/orcl/redo03a.log' - file cannot be created
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
SQL>
SQL> !mkdir /oradata/orcl
SQL>
SQL> alter database add logfile group 3('/oradata/orcl/redo03a.log','/oradata/orcl/redo03b.log') size 20M;
Database altered.
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
2 ONLINE /u01/app/oracle/fast_recovery_ YES
area/XE/onlinelog/o1_mf_2_jb3k
3nrp_.log
1 ONLINE /u01/app/oracle/fast_recovery_ YES
area/XE/onlinelog/o1_mf_1_jb3k
3nkl_.log
3 ONLINE /oradata/orcl/redo03a.log NO
3 ONLINE /oradata/orcl/redo03b.log NO
SQL>
10.3 添加日志成员¶
#1.创建日志组
alter database add logfile group 5 ('/oradata/redo05a.log') size 20M;
select * from v$logfile where group#=5;
#2.给日志组添加日志成员
alter database add logfile member '/oradata/redo05b.log' to group 5;
select * from v$logfile where group#=5;
SQL> alter database add logfile group 5 ('/oradata/redo05a.log') size 20M;
Database altered.
SQL>
SQL> select * from v$logfile where group#=5;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
5 ONLINE /oradata/redo05a.log NO
SQL>
SQL>
SQL> alter database add logfile member '/oradata/redo05b.log' to group 5;
Database altered.
SQL>
SQL>
SQL> select * from v$logfile where group#=5;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
5 ONLINE /oradata/redo05a.log NO
5 INVALID ONLINE /oradata/redo05b.log NO
SQL>
#没有指定大小，添加失败！！！
#再次测试：alter database add logfile member '/oradata/redo05c.log' size 20m to group 5;
SQL> alter database add logfile member '/oradata/redo05c.log' size 20m to group 5;
alter database add logfile member '/oradata/redo05c.log' size 20m to group 5
*
ERROR at line 1:
ORA-00946: missing TO keyword
SQL>
#失败。那应该如何添加？？？？
10.4 删除日志组¶
#1.检查日志组状态
select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
#2.删除日志组的2个条件
#1.archived状态为yes
#2.status状态为inactive or unused
#3.删除日志组
alter database drop logfile group 5;
SQL> select group#,thread#,sequence#,bytes/1024/1024
size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time fro v$log; 2
size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time fro v$log
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL>
#发现from写错了。。。
SQL>
SQL>
SQL> desc v$log
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
SQL>
SQL>
SQL>
SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
GROUP# THREAD# SEQUENCE# SIZE_MB MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 1 50 1 NO CURRENT 386632 2021-05-20 17:00:59
2 1 0 50 1 YES UNUSED 0
3 1 0 20 2 YES UNUSED 0
5 1 0 20 2 YES UNUSED 0
SQL>
SQL> alter database drop logfile group 5;
Database altered.
SQL>
SQL> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
GROUP# THREAD# SEQUENCE# SIZE_MB MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 1 50 1 NO CURRENT 386632 2021-05-20 17:00:59
2 1 0 50 1 YES UNUSED 0
3 1 0 20 2 YES UNUSED 0
SQL>
11. redolog损坏的恢复（重点！！！）¶
11.1 使用全备脚本进行全备（失败了。）¶
mkdir /oradata/backup
cd /oradata/backup
vim full.sh
sh full.sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export ORACLE_SID=XE
#以上3条要与自己电脑上匹配
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH
#自己加的备份目录变量，改造一下
export BACKUP_DIR=/oradata/backup
#没有日志目录会自己创建吗？发现原来是自己的命令抄写错了！不是连在一起的目录！
rman target / log $BACKUP_DIR/rman_full.log append <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p';
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
}
EOF
-bash-4.2$ mkdir /oradata/backup
-bash-4.2$ cd /oradata/backup
-bash-4.2$
-bash-4.2$ vim full.sh
-bash-4.2$
-bash-4.2$ sh full.sh
Recovery Manager: Release 11.2.0.2.0 - Production on Fri May 21 10:24:15 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied
-bash-4.2$#
-bash-4.2$
#报错如何解决？？？
#发现原来是之前的脚本有错误，修改后执行
-bash-4.2$ cat full.sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH
export BACKUP_DIR=/oradata/backup
rman target / log /u01/backup/rman_full.log append <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p';
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
}
EOF
#修改此行的log前后都有空格：rman target / log /u01/backup/rman_full.log append <<EOF
-bash-4.2$ vim full.sh
-bash-4.2$
-bash-4.2$ sh full.sh
RMAN-00557: could not open MSGLOG "/u01/backup/rman_full.log"
Recovery Manager: Release 11.2.0.2.0 - Production on Fri May 21 11:43:23 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2953896626)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=18 device type=DISK
RMAN-06908: WARNING: operation will not run in parallel on the allocated channels
RMAN-06909: WARNING: parallelism require Enterprise Edition
allocated channel: c2
channel c2: SID=33 device type=DISK
Starting backup at 21-MAY-21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/XE/sysaux.dbf
input datafile file number=00012 name=/oradata/test08.dbf
channel c1: starting piece 1 at 21-MAY-21
channel c1: finished piece 1 at 21-MAY-21
piece handle=/oradata/backup/full_XE_20210521_1_1 tag=TAG20210521T114325 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/XE/system.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/XE/undotbs1.dbf
input datafile file number=00013 name=/oradata/test01_2.dbf
input datafile file number=00005 name=/oradata/test.dbf
channel c1: starting piece 1 at 21-MAY-21
channel c1: finished piece 1 at 21-MAY-21
piece handle=/oradata/backup/full_XE_20210521_2_1 tag=TAG20210521T114325 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/XE/users.dbf
input datafile file number=00007 name=/oradata/test02.dbf
input datafile file number=00009 name=/oradata/test04.dbf
input datafile file number=00011 name=/oradata/test06.dbf
channel c1: starting piece 1 at 21-MAY-21
channel c1: finished piece 1 at 21-MAY-21
piece handle=/oradata/backup/full_XE_20210521_3_1 tag=TAG20210521T114325 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00014 name=/oradata/test01_3.dbf
input datafile file number=00006 name=/oradata/test01.dbf
input datafile file number=00008 name=/oradata/test03/test03_1.dbf
input datafile file number=00010 name=/oradata/test05.dbf
channel c1: starting piece 1 at 21-MAY-21
channel c1: finished piece 1 at 21-MAY-21
piece handle=/oradata/backup/full_XE_20210521_4_1 tag=TAG20210521T114325 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 21-MAY-21
channel c1: finished piece 1 at 21-MAY-21
piece handle=/oradata/backup/full_XE_20210521_5_1 tag=TAG20210521T114325 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 21-MAY-21
channel c1: finished piece 1 at 21-MAY-21
piece handle=/oradata/backup/full_XE_20210521_6_1 tag=TAG20210521T114325 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-MAY-21
sql statement: alter system archive log current
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/21/2021 11:43:42
RMAN-11003: failure during parse/execution of SQL statement: alter system archive log current
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 1 thread 1: '/u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_jb3k3nkl_.log'
RMAN>
Recovery Manager complete.
-bash-4.2$
#依旧报错。发现 rman target / log /u01/backup/rman_full.log append <<EOF 目录错误，修改为
# rman target / log $BACKUP_DIR/rman_full.log append <<EOF
#再次测试
-bash-4.2$ ls
full.sh full_XE_20210521_1_1 full_XE_20210521_2_1 full_XE_20210521_3_1 full_XE_20210521_4_1 full_XE_20210521_5_1 full_XE_20210521_6_1
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$ rm -rf full_XE_20210521_*
-bash-4.2$
-bash-4.2$ ls
full.sh
-bash-4.2$
-bash-4.2$ vim full.sh
-bash-4.2$
-bash-4.2$ cat full.sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export ORACLE_SID=XE
#以上3条要与自己电脑上匹配
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH
#自己加的备份目录变量，改造一下
export BACKUP_DIR=/oradata/backup
#没有日志目录会自己创建吗？发现原来是自己的命令抄写错了！不是连在一起的目录！
rman target / log $BACKUP_DIR/rman_full.log append <<EOF
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p';
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
delete noprompt expired archivelog all;
}
EOF
-bash-4.2$
-bash-4.2$ sh full.sh
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
RMAN> -bash-4.2$
-bash-4.2$
-bash-4.2$ ls
full.sh full_XE_20210521_10_1 full_XE_20210521_11_1 full_XE_20210521_12_1 full_XE_20210521_7_1 full_XE_20210521_8_1 full_XE_20210521_9_1 rman_full.log
-bash-4.2$
-bash-4.2$
#貌似成功了。为啥没有输出是否完成？
#查看日志文件rman_full.log
11.2 刷写redolog，强制归档¶
#1.检查
select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
#2.刷写(多执行几次)
alter system archive log current;
#3.再次查看
select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
11.3 删除非current状态的redolog¶
#1.查看非当前日志组有哪些
select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
select a.group#,a.thread#,b.member,a.bytes/1024/1024 size_MB,a.members,a.archived,a.status from v$log a,v$logfile b where a.group#=b.group#;
#2.删除非当前日志组
shutdown immediate
!rm -rf /oradata/orcl/redo03b.log
#3.启动
startup
#删除非current状态的redolog日志，数据库是可以正常启动的，但是alert log日志中有报错
#4.查看alter log日志
cd /u01/app/oracle/diag/rdbms/xe/XE/trace
tail /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log
#5.检查日志状态
select a.group#,a.thread#,b.member,a.bytes/1024/1024 size_MB,a.members,a.archived,a.status from v$log a,v$logfile b where a.group#=b.group#;
11.4 恢复非当前redo日志组损坏¶
#1.使用alter database clear方式重建group
alter database clear logfile group3;
#2.查看日志组
select * from v$logfile;
#3.检查日志组状态
select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
#非当前日志组损坏的情况是比较好恢复的！！！
11.5 模拟当前redo日志组损坏¶
#1.查看当前日志组 是哪个
select * from v$log;
#2.关库
shutdown immediate
#3.删除当前日志组
!rm -rf 当前日志组路径
#4.尝试启动
startup
#发现报错。只能通过备份恢复！！！
11.6 恢复当前redo日志组损坏¶
#方法一：如果有归档和备份，用不完全恢复
#1.启动到mount阶段
startup mount
#2.使用归档日志尽可能多的恢复（生产中需要执行2遍）
recover database until cancel;
#3.用resetlogs打开数据（老师貌似没有执行这一步！！！）
alter database open resetlogs;
#4.查看日志组信息
select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
select a.group#,a.thread#,b.member,a.bytes/1024/1024 size_MB,a.members,a.archived,a.status from v$log a,v$logfile b where a.group#=b.group#;
#方法二：强制恢复，这种方法可能会导致数据不一致（不推荐）
startup mount
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
recover database until cancel;
alter database open resetlogs;
#注：使用该隐含参数将库起来后，需要将数据导出，然后重新建库。
#修复好后，需要立即将数据进行全备！！！
最后更新:
2022-02-20 08:44:07