Oracle 10g AND Oracle 11g manual database creation case -- Oracle 11g
System Environment:
Operating System: RedHat EL6
Oracle: Oracle 10g and Oracle 11g
Manual database creation is relatively easy to implement. In this case, we will make a simple comparison from 10g and 11g. We can see a simple difference between 11g and 10g!
Case 2:
Manual database creation in Oracle 11g environment
DB_NAME = 'test1'
INSTANCE_NAME = 'test1'
1. Create the Instance initialization parameter file and Password File
[Oracle @ rh6 dbs] $ cat inittest1.ora
Db_name = 'test1'
Memory_target = 400 m // Oracle 11g memory added for Automatic Management
Processes = 150
Audit_file_dest = '$ ORACLE_BASE/admin/test1/adump'
Audit_trail = 'db'
DB _ block_size = 8192
Db_domain =''
Open_cursors = 300
Remote_login_passwordfile = 'clusive'
Undo_tablespace = 'undotbs1'
# You may want to ensure that control files are created on separate physical
# Devices
Control_files =/u01/app/oracle/oradata/test1/control01.ctl
Compatible = '11. 2.0'
[Oracle @ rh6 dbs] $ orapwd file = orapwtest1 password = oracle entries = 3
2. Create Database-related directories
Diagnostic directory is added for Oracle 11g, which is $ ORACLE_BASE by default.
[Oracle @ rh6 dbs] $ mkdir-p $ ORACLE_BASE/admin/test1/adump
[Oracle @ rh6 dbs] $ mkdir-p/u01/app/oracle/oradata/test1
3. Create a database creation script
[Oracle @ rh6 ~] $ Cat cr_db. SQL
Create database test1
User sys identified by oracle
User system identified by oracle
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/test1/redo01a. log') SIZE 50 M,
GROUP 2 ('/u01/app/oracle/oradata/test1/redo02a. log') SIZE 50 M
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 200
Character set zhs16gbk
DATAFILE '/u01/app/oracle/oradata/test1/system01.dbf' SIZE 325 M REUSE
Sysaux datafile '/u01/app/oracle/oradata/test1/sysaux01.dbf' SIZE 325 M REUSE
Default temporary tablespace tempts1
TEMPFILE '/u01/app/oracle/oradata/test1/temp01.dbf'
SIZE 100 M REUSE
Undo tablespace undotbs1
DATAFILE '/u01/app/oracle/oradata/test1/undotbs01.dbf'
SIZE 200 m reuse autoextend on maxsize unlimited;
4. Start the Instance and create a database
[Oracle @ rh6 ~] $ Export ORACLE_SID = test1
[Oracle @ rh6 ~] $ Sqlplus '/as sysdba'
SQL * Plus: Release 11.2.0.1.0 Production on Wed May 21 10:59:58 2014
Copyright (c) 1982,200 9, Oracle. All rights reserved.
Connected to an idle instance.
10:59:59 SYS @ test1> startup nomounted;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2213936 bytes
Variable Size 268437456 bytes
Database Buffers 142606336 bytes
Redo Buffers 4288512 bytes
11:00:12 SYS @ test1> @/home/oracle/cr_db
Database created.
Elapsed: 00:01:23. 44
11:01:51 SYS @ test1>
Database creation alarm log:
Create tablespace sysaux DATAFILE '/u01/app/oracle/oradata/test1/sysaux01.dbf' SIZE 325 M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Wed May 21 11:01:08 2014
Completed: create tablespace sysaux DATAFILE '/u01/app/oracle/oradata/test1/sysaux01.dbf' SIZE 325 M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE
Processing? /Rdbms/admin/dplsql. bsq
Processing? /Rdbms/admin/dtxnspc. bsq
Create undo tablespace UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/test1/undotbs01.dbf'
SIZE 200 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Wed May 21 11:01:23 2014
Successfully onlined Undo Tablespace 2.
Completed: create undo tablespace UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/test1/undotbs01.dbf'
SIZE 200 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Create temporary tablespace TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/test1/temp01.dbf'
SIZE 100 M REUSE
Completed: create temporary tablespace TEMPTS1 TEMPFILE '/u01/app/oracle/oradata/test1/temp01.dbf'
SIZE 100 M REUSE
Alter database default temporary tablespace TEMPTS1
Completed: alter database default temporary tablespace TEMPTS1
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Processing? /Rdbms/admin/dfmap. bsq
Processing? /Rdbms/admin/denv. bsq
Processing? /Rdbms/admin/drac. bsq
Processing? /Rdbms/admin/dsec. bsq
Processing? /Rdbms/admin/doptim. bsq
Processing? /Rdbms/admin/dobj. bsq
Processing? /Rdbms/admin/djava. bsq
Processing? /Rdbms/admin/dpart. bsq
Wed May 21 11:01:34 2014
Processing? /Rdbms/admin/drep. bsq
Processing? /Rdbms/admin/daw. bsq
Processing? /Rdbms/admin/dsummgt. bsq
Processing? /Rdbms/admin/dtools. bsq
Processing? /Rdbms/admin/dexttab. bsq
Processing? /Rdbms/admin/ddm. bsq
Processing? /Rdbms/admin/dlmnr. bsq
Processing? /Rdbms/admin/ddst. bsq
Wed May 21 11:01:43 2014
SMON: enabling tx recovery
Starting background process SMCO
Wed May 21 11:01:44 2014
SMCO started with pid = 17, OS id = 2816
Wed May 21 11:01:50 2014
Replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed May 21 11:01:50 2014
QMNC started with pid = 20, OS id = 2826
Completed: create database test1
User sys identified by ***** user system identified by ***** LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/test1/redo01a. log') SIZE 50 M,
GROUP 2 ('/u01/app/oracle/oradata/test1/redo02a. log') SIZE 50 M
MAXLOGFILES 10
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 200
Character set zhs16gbk
DATAFILE '/u01/app/oracle/oradata/test1/system01.dbf' SIZE 325 M REUSE
Sysaux datafile '/u01/app/oracle/oradata/test1/sysaux01.dbf' SIZE 325 M REUSE
Default temporary tablespace tempts1
TEMPFILE '/u01/app/oracle/oradata/test1/temp01.dbf'
SIZE 100 M REUSE
Undo tablespace undotbs1
DATAFILE '/u01/app/oracle/oradata/test1/undotbs01.dbf'
SIZE 200 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
5. Create a data dictionary
Data Dictionary script:
[Oracle @ rh6 ~] $ Cat cr_dict. SQL
@? /Rdbms/admin/catalog. SQL
@? /Rdbms/admin/catproc. SQL
Conn system/oracle
@? /Sqlplus/admin/pupbld. SQL
At 11:00:12 SYS @ test1> @/home/oracle/cr_dict. SQL
6. Create a Users tablespace and set it as the default tablespace.
The default system tablespace is system. If system is used as the default tablespace, the management and performance of the database will be affected.
12:09:56 SQL> create tablespace users
12:10:01 2 datafile '/u01/app/oracle/oradata/test1/user01.dbf' size 100 m;
Tablespace created.
12:10:41 SQL> alter database default tablespace users;
Database altered.
7. Add example cases
12:11:45 SQL> @ $ ORACLE_HOME/rdbms/admin/utlsampl. SQL
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
[Oracle @ oracle ~] $
There is no major change in the manual creation of databases from the above Oracle 10g and Oracle 11g, but new features such as the memory automatic management and diagnostic directory are added to the Oracle 11g, so the database creation changes a little bit!
This article is from the blog of "Tian Ke's blog", please be sure to keep this source http://tiany.blog.51cto.com/513694/1414608