OCM_session0手动建库实验

root

Section 0 :创建数据库(即手动建库)1. Create a database the sid name is PROD2. Don't run the Script catalog.sql and catproc.sql
参考联机文档：Reference ==> Basic Initialization Parametershttp://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams002.htm#CJAJHDED
Administrator's Guide ==> Step 7: Issue the CREATE DATABASE Statementhttp://docs.oracle.com/cd/B19306_01/server.102/b14231/create.htm#sthref242

检查环境创建密码文件创建对应目录创建pfile参数文件创建spfile参数文件创建数据库脚本执行创建数据库脚本

• 1.检查ORACLE_SID=PROD
  

[iyunv@ocm1 ~]# su - oracle[oracle@ocm1 ~]$ cat .bash_profile# .bash_profile
# Get the aliases and functionsif [ -f ~/.bashrc ]; then        . ~/.bashrcfi
# User specific environment and startup programs
export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORACLE_SID=testdbexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$HOME/bin
export PATH

[oracle@ocm1 ~]$ vi .bash_profile

# .bash_profile
# Get the aliases and functionsif [ -f ~/.bashrc ]; then        . ~/.bashrc fi                 # User specific environment and startup programs
export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORACLE_SID=PRODexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport PATH=$ORACLE_HOME/bin:$PATH    PATH=$PATH:$HOME/bin
export PATH ~   ~   ~   ~~".bash_profile" 18L, 376C written                             [oracle@ocm1 ~]$ source .bash_profile[oracle@ocm1 ~]$ cat .bash_profile# .bash_profile
# Get the aliases and functionsif [ -f ~/.bashrc ]; then        . ~/.bashrcfi
# User specific environment and startup programs
export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORACLE_SID=PRODexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$HOME/bin
export PATH[oracle@ocm1 ~]$
检查ORACLE_SID是否已经修改好[oracle@ocm1 ~]$ env |grep ORAORACLE_SID=PRODORACLE_BASE=/u01/app/oracleORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1或者[oracle@ocm1 ~]$ env |grep -i sidORACLE_SID=PROD

• 2.创建密码文件
  

[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs[oracle@ocm1 dbs]$ lltotal 32-rw-r----- 1 oracle oinstall 12920 May  3  2001 initdw.ora-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora[oracle@ocm1 dbs]$ orapwd file=orapwPROD password=oracle entries=30[oracle@ocm1 dbs]$ lltotal 40-rw-r----- 1 oracle oinstall 12920 May  3  2001 initdw.ora-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora-rw-r----- 1 oracle oinstall  5120 Mar 18 09:54 orapwPROD

• 3.创建所需要的目录
  

[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/adump[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/bdump[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/cdump[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/admin/PROD/udump[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk1[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk2/arch[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk3[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk4[oracle@ocm1 dbs]$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk5

• 4.创建参数文件
  

Reference--Basic Initialization Parameters
创建参数文件[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs[oracle@ocm1 dbs]$ pwd/u01/app/oracle/product/10.2.0/db_1/dbs[oracle@ocm1 dbs]$ vi initPROD.oraCONTROL_FILES=('/u01/app/oracle/oradata/PROD/Disk1/control01.ctl','/u01/app/oracle/oradata/PROD/Disk1/control02.ctl','/u01/app/oracle/oradata/PROD/Disk1/control03.ctl')DB_BLOCK_SIZE=8192DB_CREATE_FILE_DEST=/u01/app/oracle/oradata/PROD/Disk1DB_CREATE_ONLINE_LOG_DEST_1=/u01/app/oracle/oradata/PROD/Disk1DB_NAME=PRODJOB_QUEUE_PROCESSES=10LOG_ARCHIVE_DEST_1='location=/u01/app/oracle/oradata/PROD/Disk2/arch'PROCESSES=200SGA_TARGET=500MBACKGROUND_DUMP_DEST=/u01/app/oracle/admin/PROD/bdumpCORE_DUMP_DEST=/u01/app/oracle/admin/PROD/cdumpUSER_DUMP_DEST=/u01/app/oracle/admin/PROD/udumpUNDO_MANAGEMENT=autoUNDO_TABLESPACE=undotbs1UNDO_RETENTION=5400~~"initPROD.ora" [New] 16L, 705C written

• 5.使用pfile启动到nomount状态。创建spfile，使用spfile来强制启动，startup force nomount.
  

[oracle@ocm1 dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 18 10:09:25 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomountORACLE instance started.
Total System Global Area  524288000 bytesFixed Size                  1220360 bytesVariable Size             146800888 bytesDatabase Buffers          373293056 bytesRedo Buffers                2973696 bytesSQL> create spfile from pfile;
File created.
此时是以pfile启动的SQL> show parameters spfile
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------spfile                               string或者查询ISSPECIFIED为false就是pfile启动的。SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC------FALSE
强制重新启动SQL> startup force nomountORACLE instance started.
Total System Global Area  524288000 bytesFixed Size                  1220360 bytesVariable Size             146800888 bytesDatabase Buffers          373293056 bytesRedo Buffers                2973696 bytes
查看是以spfile文件启动的SQL> show parameters spfile
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------spfile                               string      /u01/app/oracle/product/10.2.0/db_1/dbs/spfilePROD.ora
或者查看ISSPECIFIED，有true即为spfile启动的数据库SQL> select distinct ISSPECIFIED from v$spparameter;
ISSPEC------FALSETRUE
SQL>

• 6.创建数据库脚本
  

SQL> CREATE DATABASE PROD  2     USER SYS IDENTIFIED BY oracle  3     USER SYSTEM IDENTIFIED BY oracle  4     LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01.log') SIZE 100M,  5             GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo02.log') SIZE 100M,  6             GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo03.log') SIZE 100M  7     MAXLOGFILES 5  8     MAXLOGMEMBERS 5  9     MAXLOGHISTORY 1 10     MAXDATAFILES 100 11     MAXINSTANCES 1 12     CHARACTER SET AL32UTF8 13     NATIONAL CHARACTER SET AL16UTF16 14     DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE 15     EXTENT MANAGEMENT LOCAL 16     SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE 17     DEFAULT TEMPORARY TABLESPACE tempts1 18        TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf' 19        SIZE 20M REUSE 20     UNDO TABLESPACE undotbs1 21        DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf' 22        SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
在数据库创建时可以查看下告警日志[iyunv@ocm1 ~]# su - oracle[oracle@ocm1 ~]$ cd /u01/app/oracle/admin/PROD/bdump[oracle@ocm1 bdump]$ lltotal 4-rw-r--r-- 1 oracle oinstall 3488 Mar 18 10:11 alert_PROD.log[oracle@ocm1 bdump]$ tail -f alert_PROD.log Tue Mar 18 10:18:51 2014CREATE DATABASE PROD   USER SYS IDENTIFIED BY ****USER SYSTEM IDENTIFIED BY ****LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01.log') SIZE 100M,           GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo02.log') SIZE 100M,           GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo03.log') SIZE 100M   MAXLOGFILES 5   MAXLOGMEMBERS 5   MAXLOGHISTORY 1   MAXDATAFILES 100   MAXINSTANCES 1   CHARACTER SET AL32UTF8   NATIONAL CHARACTER SET AL16UTF16   DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE   EXTENT MANAGEMENT LOCAL   SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE   DEFAULT TEMPORARY TABLESPACE tempts1      TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'      SIZE 20M REUSE   UNDO TABLESPACE undotbs1      DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'      SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDTue Mar 18 10:18:53 2014Database mounted in Exclusive ModeTue Mar 18 10:19:16 2014Successful mount of redo thread 1, with mount id 254364939Assigning activation ID 254364939 (0xf294d0b)Thread 1 opened at log sequence 1  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/PROD/Disk1/redo01.logSuccessful open of redo thread 1Tue Mar 18 10:19:16 2014MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setTue Mar 18 10:19:16 2014SMON: enabling cache recoveryTue Mar 18 10:19:16 2014create tablespace SYSTEM datafile  '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE     EXTENT MANAGEMENT LOCAL onlineTue Mar 18 10:19:38 2014Completed: create tablespace SYSTEM datafile  '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE     EXTENT MANAGEMENT LOCAL onlineTue Mar 18 10:19:38 2014create rollback segment SYSTEM tablespace SYSTEM  storage (initial 50K next 50K)Completed: create rollback segment SYSTEM tablespace SYSTEM  storage (initial 50K next 50K)Tue Mar 18 10:19:55 2014CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'      SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDTue Mar 18 10:20:04 2014Successfully onlined Undo Tablespace 1.Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE  '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'      SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDTue Mar 18 10:20:04 2014create tablespace SYSAUX datafile  '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE     EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO onlineTue Mar 18 10:20:24 2014Completed: create tablespace SYSAUX datafile  '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE     EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO onlineTue Mar 18 10:20:25 2014CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE  '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'      SIZE 20M REUSE   Completed: CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE  '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'      SIZE 20M REUSE   Tue Mar 18 10:20:25 2014ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1Tue Mar 18 10:20:26 2014ALTER DATABASE DEFAULT TABLESPACE SYSTEM Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM Tue Mar 18 10:20:33 2014SMON: enabling tx recoveryTue Mar 18 10:20:37 2014Threshold validation cannot be done before catproc is loaded.replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=14, OS id=3903Tue Mar 18 10:20:39 2014Completed: CREATE DATABASE PROD   USER SYS IDENTIFIED BY ****USER SYSTEM IDENTIFIED BY ****LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01.log') SIZE 100M,           GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo02.log') SIZE 100M,           GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo03.log') SIZE 100M   MAXLOGFILES 5   MAXLOGMEMBERS 5   MAXLOGHISTORY 1   MAXDATAFILES 100   MAXINSTANCES 1   CHARACTER SET AL32UTF8   NATIONAL CHARACTER SET AL16UTF16   DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE   EXTENT MANAGEMENT LOCAL   SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE   DEFAULT TEMPORARY TABLESPACE tempts1      TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'      SIZE 20M REUSE   UNDO TABLESPACE undotbs1      DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'      SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

TIMES: 45 minutes
1. Database Setup and Undo Management 1.1 Run the minimum required scripts to complete the basic configuration of the PROD database.1.2 Set up automatic undo management in the PROD database to support the following requirements:  1.2.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 90 minues on average.  1.2.2 The number or concurrent OLTP users will be approximately 120 during normal business hours.  1.2.3 The number or concurrent batch processes that will run in the evenings and weekends will approximately 12 to 15  

• 7.跑脚本，跑catalog和catporc这两个脚本
  

• 1.1 Run the minimum required scripts to complete the basic configuration of the PROD database.
  

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sqlSQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
PL/SQL procedure successfully completed.
SQL> select open_mode from v$database;
OPEN_MODE----------READ WRITE
1 row selected.

• 1.2.1 Avoid ORA-01555 Snapshot too old errors for queries running up to 90 minues on average.
  

SQL> alter system set undo_retention=5400;
System altered.

• 1.2.2 The number or concurrent OLTP users will be approximately 120 during normal business hours.
  

SQL> alter system set processes=135 scope=spfile;
System altered.

• 1.2.3 The number or concurrent batch processes that will run in the evenings and weekends will approximately 12 to 15  
  

SQL> alter system set job_queue_processes=15;
System altered.
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.
Total System Global Area  524288000 bytesFixed Size                  1220360 bytesVariable Size             150995192 bytesDatabase Buffers          369098752 bytesRedo Buffers                2973696 bytesDatabase mounted.Database opened.SQL>
         版权声明：本文为博主原创文章，未经博主允许不得转载。