①关闭现有数据库:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--查看参数文件:
[oracle@wang ~]$ cd $ORACLE_HOME/dbs
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
--生成pfile文件:
[oracle@wang dbs]$ cat init.ora |grep -v ^# |grep -v ^$ > initPROD.ora
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora [oracle@wang dbs]$
--修改initPROD.ora文件:
[oracle@wang 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@wang dbs]$ mkdir -p /u01/app/oracle/admin/PROD/adump
[oracle@wang dbs]$ mkdir -p /u01/app/oracle/oradata/PROD/
[oracle@wang dbs]$ cd $ORACLE_BASE
[oracle@wang oracle]$ ls
admin cfgtoollogs checkpoints diag oradata product
[oracle@wang oracle]$ cd admin/
[oracle@wang admin]$ ls
ORA11GR2 prod
[oracle@wang admin]$ cd ..
[oracle@wang oracle]$ cd oradata/
[oracle@wang oradata]$ ls
ORA11GR2 PROD
[oracle@wang oradata]$
通过pfile创建spfile:
--查看已存在的pfile文件initPROD.ora
oracle@wang dbs]$ ls hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora
--验证当前环境变量
[oracle@wang oradata]$ echo $ORACLE_SID
ORA11GR2
--更改当前环境变量ORACLE_SID:
[oracle@wang oradata]$ export ORACLE_SID=PROD
[oracle@wang oradata]$ echo $ORACLE_SID
PROD
--通过pfile创建spfile
SQL> create spfile from pfile;
File created.
验证生成spfile:
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat init.ora initORA11GR2.ora initPROD.ora lkORA11GR2 orapwORA11GR2 spfileORA11GR2.ora spfilePROD.ora
②数据库启动到nomount(默认情况下,oracle会使用spfile启动数据库):
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ -----------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD.ora
SQL> select status from v$instance;
STATUS
------------
STARTED
--编写创建数据库脚本:
[oracle@wang dbs]$ cd $ORACLE_HOME
[oracle@wang dbhome_1]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@wang dbhome_1]$
[oracle@wang dbhome_1]$
[oracle@wang 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> SET SQLBLANKLINES ON
SQL> @?/create
Database created.
--查看相关参数:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ -----------
control_files string /u01/app/oracle/oradata/PROD/c
ontrol01.ctl, /u01/app/oracle/
oradata/PROD/control02.ctl
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ -----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ -----------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilePROD.ora
③创建字典表及工具包
--必执行脚本
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
SQL> @?/rdbms/admin/utlsampl 添加scott用户
④验证数据库可用性:
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 rows selected.
⑤创建密码文件:(可建可不建)
[oracle@wang dbhome_1]$ cd dbs/
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat initPROD.ora spfileORA11GR2.ora
hc_PROD.dat lkORA11GR2 spfilePROD.ora
init.ora lkPROD
initORA11GR2.ora orapwORA11GR2
[oracle@wang dbs]$ orapwd file=orapwPROD password=oracle
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat initPROD.ora orapwPROD
hc_PROD.dat lkORA11GR2 spfileORA11GR2.ora
init.ora lkPROD spfilePROD.ora
initORA11GR2.ora orapwORA11GR2
[oracle@wang dbs]$