关闭现有数据库:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
查看参数文件:
[oracle@host2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@host2 dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
1.创建实例instance:
生成pfile文件:
[oracle@host2 dbs]$ cat init.ora |grep -v ^# |grep -v ^$ > initPROD.ora
[oracle@host2 dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
修改initPROD.ora 文件:
[oracle@host2 dbs]$ vi initPROD.ora
db_name='PROD'
memory_target=800M
processes = 150
audit_file_dest='/u01/app/oracle/admin/PROD/adump'
#audit_trail ='db'
db_block_size=8192
db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
#db_recovery_file_dest_size=2G
#diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = (/u01/app/oracle/oradata/PROD/control1.ctl, /u01/app/oracle/oradata/PROD/control2.ctl)
根据pfile文件并创建审计文件adump、控制文件:
[oracle@host2 dbs]$ mkdir -p /u01/app/oracle/admin/PROD/adump
[oracle@host2 dbs]$ mkdir -p /u01/app/oracle/oradata/PROD/
查看admin目录下文件:ORA11GR2 PROD
[oracle@host2 dbs]$ cd $ORACLE_BASE
[oracle@host2 oracle]$ cd admin/
[oracle@host2 admin]$ ls
ORA11GR2 PROD
查看oradata目录下文件:
[oracle@host2 admin]$ cd .. (返回上级目录)
[oracle@host2 oracle]$ cd oradata/
[oracle@host2 oradata]$ ls
ORA11GR2 PROD
[oracle@host2 oradata]$
ls 验证生成的 initPROD.ora pfile文件;
[oracle@host2 dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
2.创建spfile:
验证当前环境变量:
[oracle@host2 ~]$ echo $ORACLE_SID
ORA11GR2
切换当前环境变量:
[oracle@host2 ~]$ export ORACLE_SID=PROD
[oracle@host2 ~]$ echo $ORACLE_SID
PROD
[oracle@host2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 4 19:58:40 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
通过pfile创建spfile:
SQL> create spfile from pfile;
File created.
ls验证生成spfilePROD.ora spfile:
[oracle@host2 dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora spfilePROD.ora
3.通过create database 创建database
数据库启动到nomount状态:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
SQL> select status from v$instance;
STATUS
------------
STARTED
编写创建数据库脚本:
[oracle@host2 ~]$ cd $ORACLE_HOME
[oracle@host2 dbhome_1]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@host2 dbhome_1]$ vi create.sql
CREATE DATABASE PROD
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log','/u01/app/oracle/oradata/PROD/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log','/u01/app/oracle/oradata/PROD/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.log','/u01/app/oracle/oradata/PROD/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/PROD/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/PROD/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
运行数据库脚本:
SQL> @?/create
Database created.
4.创建数据字典
必执行的脚本:
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/oracle
SQL> @?/sqlplus/admin/pupbld.sql
可选脚本:
SQL> conn / as sysdba
SQL> @?/rdbms/admin/catblock.sql
SQL> @?/rdbms/admin/catoctk.sql
SQL> @?/rdbms/admin/owminst.plb
创建scott用户:
SQL> @?/rdbms/admin/utlsampl
验证数据库可用性:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
5.口令文件:(也可不创建)
[oracle@host2 dbs]$ orapwd file=orapwPROD password=oracle
[oracle@host2 dbs]$ ls
hc_ORA11GR2.dat init.ora initPROD.ora lkPROD orapwPROD spfilePROD.ora
hc_PROD.dat initORA11GR2.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora