Oracle数据库创建实例
数据库已经安装完成,可以正常登陆查看用户等操作
system用户只能用normal身份登陆em。除非你对它授予了sysdba的系统权限或者syspoer系统权限。
sys用户具有“SYSDBA”或者“SYSOPER”权限,登陆em也只能用这两个身份,不能用normal。
sys所有oracle的数据字典的基表和视图都存放在sys用户中,这些基表和视图对于oracle的运行是至关重要的,由数据库自己维护,任何用户都不能手动更改。sys用户拥有dba,sysdba,sysoper等角色或权限,是oracle权限最高的用户。
system用户用于存放次一级的内部数据,如oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。
检查数据库
[root@oracledb ~]# su – oracle
[oracle@oracledb ~]$ cat ./.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
umask 022
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
创建新的实例:ORCL
1. 首先设置要创建的ORACLE的SID
[oracle@oracledb ~]$ export ORACLE_SID=ORCL
2、 创建相应目录
[oracle@oracledb ~]$ mkdir -p $ORACLE_BASE/admin/ORCL/{a,b,c,u}dump
[oracle@oracledb ~]$ mkdir -p $ORACLE_BASE/admin/ORCL/pfile
[oracle@oracledb ~]$ mkdir -p $ORACLE_BASE/oradata/ORCLmkdir
[oracle@oracledb ~]$ mkdir -p $ORACLE_BASE/fast_recovery_area
3、在$ORACLE_HOME/dbs目录下创建初始化文件
命名方法:init实例名.ora 本例中initORCL.ora
[oracle@oracledb ~]$ cd $ORACLE_HOME/dbs
[oracle@oracledb dbs]$ cp init.ora initORCL.ora
[oracle@oracledb dbs]$ vi initORCL.ora
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
注意不要用原来的<ORACLE_BASE>作为路径,修改为对应的绝对路径,否则会报错
4、创建密码文件
[oracle@oracledb dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=IMRTS entries=5 force=y
5、创建oracle的建库脚本 createdb.sql,内容如下,将其放在了$ORACLE_BASE/oradata/ORCL下面
[oracle@oracledb dbs]$ cd $ORACLE_BASE/oradata/ORCL
[oracle@oracledb ORCL]$ vi createdb.sql
CREATE DATABASE ORCL
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited extent management local
sysaux datafile '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited
default temporary tablespace TEMP tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' size 20m reuse autoextend on next 640k maxsize unlimited
undo tablespace UNDOTBS1 datafile '/u01/app/oracle/oradata/ORCL/undo01.dbf' size 20m reuse autoextend on next 5M maxsize unlimited
logfile
GROUP 1 ('/u01/app/oracle/oradata/ORCL/redo1.dbf') size 10m,
GROUP 2 ('/u01/app/oracle/oradata/ORCL/redo2.dbf') size 10m,
GROUP 3 ('/u01/app/oracle/oradata/ORCL/redo3.dbf') size 10m
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
字符集后面根据需要也可以修改
SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBAL32UTF8
*
ERROR at line 1:
ORA-12715: invalid character set specified
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;
Database altered.
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP
6、执行建库和数据字典脚本
以sysdba进入:
[oracle@oracledb dbs]$ sqlplus / as sysdba
依次执行以下命令
startup nomount;
@$ORACLE_BASE/oradata/addb/createdb.sql
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catexp.sql;
7、修改监听配置文件listener.ora
到$ORACLE_HOME/network/admin目录下:vi listener.ora
[oracle@oracledb addb]$ cd $ORACLE_HOME/network/admin
[oracle@oracledb admin]$ vi listener.ora
ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1522))
)
)
)
SID_LIST_ORCL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = ORCL)
)
)
启动并查看监听状态
[oracle@oracledb admin]$ lsnrctl start addb
[oracle@oracledb admin]$ lsnrctl status addb
8、创建user表空间
[oracle@oracledb dbs]$ sqlplus / as sysdba
SQL> select name from v$database;
SQL> CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/u01/app/oracle/oradata/ORCL/user01.dbf' SIZE 1000M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";
SQL> create spfile from pfile;
SQL> conn system
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/pupbld.sql
SQL> conn sysdba
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/help/hlpbld.sql helpus.sql
SQL> select * from all_tab_comments;
SQL> select * from user_tab_comments;
SQL> select * from all_col_comments;
Oracle数据库创建角色、远程连接授权
1、创建的用户不区分大小写,默认显示为大写
SQL > create user imrts identified by IMRTS; //创建用户imrts,密码IMRTS
SQL > drop user xxx; //删除用户
SQL > drop user xxx cascade; //删除用户,用户拥有数据表情况下使用
2、取消密码验证错误自动锁定账号功能
SQL > alter user imrts account unlock;
oracle的默认设定是登陆时密码错几次之后,系统自动锁定该用户,此时可以用这个命令对该用户解锁
3、分配操作数据库权限
SQL> grant create user, drop user, alter user, create any view, drop any view,exp_full_database,imp_full_database,dba,connect,resource,create session to imrts;
4、更改用户密码为无期限,密码默认使用180天
查询数据库用户密码的profile(一般为default):
SQL> SELECT username,PROFILE FROM dba_users;
查看defult的时间:
SQL> SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
修改defult的时间:
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
修改完,直接生效不需要重启。
远程连接
重启实例:
(1) 切换需要启动的数据库实例:export ORACLE_SID=ORCL
(2) 进入sqlplus控制台,命令:sqlplus /nolog
(3) 以系统管理员登录,命令:connect / as sysdba
(4) 如果是关闭数据库,命令:shutdown abort
(5) 启动数据库,命令:startup
(6) 退出sqlplus控制台,命令:exit