数据库管理和维护常用操作和命令
数据库管理和维护常用操作和命令
##数据库相关操作命令
1.创建LMT 本地管理的表空间
CREATE TABLESPACE tbs_01
DATAFILE 'tbs_f2.dbf' SIZE 5M ;
select file#, bytes, name from v$datafile where name like '%tbs_f2%';
>/u01/app/oracle/product/11.2.0/dbhome_1/dbs/tbs_f2.dbf
alter database datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/tbs_f2.dbf' resize 50 M;
或者
alter tablespace tbs_01 add datafile 'tbs_f3.dbf' size 45M;
2.创建Bigfile表空间
CREATE BIGFILE TABLESPACE bigtbs_01
DATAFILE 'bigtbs_f1.dbf'
SIZE 20M;
DROP TABLESPACE bigtbs_01 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tbs_01 INCLUDING CONTENTS AND DATAFILES;
Refer：
Home / Database / Oracle Database Online Documentation 11g Release 2 (11.2) / Database Administration
Database SQL Language Reference
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7003.htm#SQLRF01403
>CREATE TABLESPACE
3.配置网络
cd $ORACLE_HOME/network/admin
/connect string：tnsnames.ora
testorcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.us.oracle.com)
)
)
/listener：listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PCNAME.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PCNAME.us.oracle.com)(PORT = 1561))
)
)
SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(GLOBAL_DBNAME = orcl.us.oracle.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
lsnrctl start LISTENER2
lsnrctl status LISTENER24.共享（Shared）和专有(dedicated)服务器连接
确认相关信息的方法
--进程数
$ ps -ef | grep oracle |wc -l
119
--连接服务器的内容
select username,server from v$session where username ='SYSTEM';
--共享服务器连接相关设定
SYS@TEST1 09-JAN-18> show parameter shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_servers integer 1
SYS@TEST1 09-JAN-18> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=ORCLXDB)
max_dispatchers integer
alter system set shared_servers=2;
alter system set dispatchers='(PROTOCOL=TCP) (DISPATCHERS=2)'
SYS@orcl 09-JAN-18> show parameter shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_servers integer 2
SYS@orcl 09-JAN-18> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (DISPATCHERS=2)
max_dispatchers integer
EZConnect方法：
sqlplus system/oracle@11.11.11.11:1521/orcl.us.oracle.com
select username,server from v$session where username ='SYSTEM';
TNS的设定方法：
共有服务器接续：(SERVICE = shared)
专有服务器接续：(SERVER = dedicated) 默认
shared_orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE = shared)
(SERVICE_NAME = orcl.us.oracle.com)
)
)
$ sqlplus system/oracle@shared_orcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 06:16:05 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SYSTEM@shared_orcl 09-JAN-18> select username,server from v$session where sid in (select sid from v$mystat);
USERNAME SERVER
------------------------------ ---------
SYSTEM SHARED
5.密码的大小写区分控制参数sec_case_sensitive_logon
SYS@orcl 09-JAN-18> show parameter sec_case
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SYS@orcl 09-JAN-18> conn hr/hr
Connected.
HR@orcl 09-JAN-18> conn HR/HR
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
@ 09-JAN-18> conn hr/HR
ERROR:
ORA-01017: invalid username/password; logon denied
@ 09-JAN-18> conn / as sysdba
Connected.
SYS@orcl 09-JAN-18> alter system set sec_case_sensitive_logon=false;
System altered.
SYS@orcl 09-JAN-18> show parameter sec_case
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SYS@orcl 09-JAN-18> conn hr/HR
Connected.
HR@orcl 09-JAN-18> conn hr/hr
Connected.
HR@orcl 09-JAN-18>
6.password file 密码的大小不区分.
使用orapwd的ignorecase参数进行控制
SYSTEM@orcl 09-JAN-18> conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl 09-JAN-18> conn sys/ORACLE@orcl as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
@ 09-JAN-18> !ls $ORACLE_HOME/dbs/
Warning: You are no longer connected to ORACLE.
$ rm $ORACLE_HOME/dbs/orapworcl
$ orapwd file=$ORACLE_HOME/dbs/orapworcl entries=30 password=oracle ignorecase=Y force=Y
@ 09-JAN-18> conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl 09-JAN-18> conn sys/ORACLE@orcl as sysdba
Connected.
$ rm $ORACLE_HOME/dbs/orapworcl
$ orapwd file=$ORACLE_HOME/dbs/orapworcl entries=30 password=oracle ignorecase=N force=Y
SYS@orcl 09-JAN-18> conn sys/oracle@orcl as sysdba
Connected.
SYS@orcl 09-JAN-18> conn sys/ORACLE@orcl as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
参考：
Oracle® Database Administrator's Guide11g Release 2 (11.2) E25494-03
>Creating and Maintaining a Password File
##操作过程遇到的错误（踩到的坑）：
配置SID连接，tns错误：
tns:
testorcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
(CONNECT_DATA =
(SID = orcl)
)
)
[oracle@PCNAME-TEST1 admin]$ sqlplus system/oracle@testorcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 05:18:13 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
$ tnsping testorcl
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 09-JAN-2018 05:24:10
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
TNS-12533: TNS:illegal ADDRESS parameters
解决：
修改tnsname配置如下：
testorcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
[oracle@PCNAME-TEST1 admin]$ tnsping testorcl
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 09-JAN-2018 05:26:57
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))) (CONNECT_DATA = (SID = orcl)))
OK (0 msec)
[oracle@PCNAME-TEST1 admin]$ sqlplus system/oracle@testorcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 05:27:09 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SYSTEM@testorcl 09-JAN-18>