OCM_session0手动建库实验
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
# su - oracle$ 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
$ 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 $ source .bash_profile$ 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_SID是否已经修改好$ env |grep ORAORACLE_SID=PRODORACLE_BASE=/u01/app/oracleORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1或者$ env |grep -i sidORACLE_SID=PROD
[*]2.创建密码文件
$ cd $ORACLE_HOME/dbs$ lltotal 32-rw-r----- 1 oracle oinstall 12920 May32001 initdw.ora-rw-r----- 1 oracle oinstall8385 Sep 111998 init.ora$ orapwd file=orapwPROD password=oracle entries=30$ lltotal 40-rw-r----- 1 oracle oinstall 12920 May32001 initdw.ora-rw-r----- 1 oracle oinstall8385 Sep 111998 init.ora-rw-r----- 1 oracle oinstall5120 Mar 18 09:54 orapwPROD
[*]3.创建所需要的目录
$ mkdir -p $ORACLE_BASE/admin/PROD/adump$ mkdir -p $ORACLE_BASE/admin/PROD/bdump$ mkdir -p $ORACLE_BASE/admin/PROD/cdump$ mkdir -p $ORACLE_BASE/admin/PROD/udump$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk1$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk2/arch$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk3$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk4$ mkdir -p $ORACLE_BASE/oradata/PROD/Disk5
[*]4.创建参数文件
Reference--Basic Initialization Parameters
创建参数文件$ cd $ORACLE_HOME/dbs$ pwd/u01/app/oracle/product/10.2.0/db_1/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" 16L, 705C written
[*]5.使用pfile启动到nomount状态。创建spfile，使用spfile来强制启动，startup force nomount.
$ 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 Area524288000 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 Area524288000 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 PROD2 USER SYS IDENTIFIED BY oracle3 USER SYSTEM IDENTIFIED BY oracle4 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 100M7 MAXLOGFILES 58 MAXLOGMEMBERS 59 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.
在数据库创建时可以查看下告警日志# su - oracle$ cd /u01/app/oracle/admin/PROD/bdump$ lltotal 4-rw-r--r-- 1 oracle oinstall 3488 Mar 18 10:11 alert_PROD.log$ 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 1Current 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 SYSTEMstorage (initial 50K next 50K)Completed: create rollback segment SYSTEM tablespace SYSTEMstorage (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 Area524288000 bytesFixed Size 1220360 bytesVariable Size 150995192 bytesDatabase Buffers 369098752 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.SQL>
版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]