Oracle 10g AND Oracle 11g manual database creation case -- Oracle 11g

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