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

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

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 1:

Manual database creation in Oracle 10g environment

DB_NAME = 'cuug'

INSTANCE_NAME = 'cuug'

1. Create the Instance initialization parameter file and password file:

[Oracle @ oracle dbs] $ cat initcuug. ora

Db_name = cuug

DB _ block_size = 8192

Pga_aggregate_target = 30 M

Db_cache_size = 80 M

Shared_pool_size = 60 M

Parallel_threads_per_cpu = 4

Optimizer_mode = choose

Star_transformation_enabled = true

Db_file_multiblock_read_count = 16

Query_rewrite_enabled = true

Query_rewrite_integrity = trusted

Background_dump_dest = $ ORACLE_BASE/admin/cuug/bdump

User_dump_dest = $ ORACLE_BASE/admin/cuug/udump

Core_dump_dest = $ ORACLE_BASE/admin/cuug/cdump

Control_files = $ ORACLE_BASE/oradata/cuug/control01.ctl

Undo_management = auto

Undo_tablespace = rtbs

[Oracle @ oracle dbs] $ orapwd file = orapwcuug password = oracle entries = 3

2. Create a database directory

[Oracle @ oracle dbs] $ mkdir-p $ ORACLE_BASE/admin/cuug/bdump

[Oracle @ oracle dbs] $ mkdir-p $ ORACLE_BASE/admin/cuug/cdump

[Oracle @ oracle dbs] $ mkdir-p $ ORACLE_BASE/admin/cuug/udump

[Oracle @ oracle dbs] $ mkdir-p $ ORACLE_BASE/oradata/cuug

3. Create a database creation script (refer to Oracle 11g Online)

[Oracle @ oracle dbs] $ cat cr_db. SQL

Create database cuug

User sys identified by oracle

User system identified by oracle

Datafile '$ ORACLE_BASE/oradata/cuug/system01.dbf' size 300 m

Sysaux datafile '$ ORACLE_BASE/oradata/cuug/sysaux01.dbf 'size 100 m

Default temporary tablespace temp tempfile '$ ORACLE_BASE/oradata/cuug/temp01.dbf' size 100 m

Undo tablespace rtbs datafile '$ ORACLE_BASE/oradata/cuug/rtbs01.dbf' size 100 m

Logfile

Group 1' $ ORACLE_BASE/oradata/cuug/redo01a. log' size 10 m,

Group 2 '$ ORACLE_BASE/oradata/cuug/redo02a. log' size 10 m

Character set zhs16gbk;

4. Start Instance to create database

[Oracle @ oracle dbs] $ export ORACLE_SID = cuug

[Oracle @ oracle dbs] $ sqlplus '/as sysdba'

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

At 11:00:12 SYS @ test1> @/home/oracle/cr_db. SQL

Database created.

View alarm log information (alert_cuug.log );

[Oracle @ oracle dbs] tail-f/u01/app/oracle/admin/cuug/bdump/alert_cuug.log

Create tablespace SYSTEM datafile '$ ORACLE_BASE/oradata/test/system01.dbf' size 400 m

Default storage (initial 10 K next 10 K) online

Sat Aug 20 00:26:34 2011

Completed: create tablespace SYSTEM datafile '$ ORACLE_BASE/oradata/test/system01.dbf' size 400 m

Default storage (initial 10 K next 10 K) extent management dictionary online

Sat Aug 20 00:26:34 2011

Create rollback segment SYSTEM tablespace SYSTEM

Storage (initial 50 K next 50 K)

Completed: create rollback segment SYSTEM tablespace SYSTEM

Storage (initial 50 K next 50 K)

Sat Aug 20 00:26:49 2011

Thread 1 advanced to log sequence 2

Current log #2 seq #2 mem #0:/u01/app/oracle/oradata/test/redo02a. log

Sat Aug 20 00:26:50 2011

Create undo tablespace rtbs datafile '$ ORACLE_BASE/oradata/test/rtbs01.dbf' size 100 m

Sat Aug 20 00:26:51 2011

Successfully onlined Undo Tablespace 1.

Completed: create undo tablespace rtbs datafile '$ ORACLE_BASE/oradata/test/rtbs01.dbf' size 100 m

Sat Aug 20 00:26:51 2011

Create tablespace SYSAUX datafile '$ ORACLE_BASE/oradata/test/sysaux01.dbf' size 100 m

Extent management local segment space management auto online

Completed: create tablespace SYSAUX datafile '$ ORACLE_BASE/oradata/test/sysaux01.dbf' size 100 m

Extent management local segment space management auto online

Sat Aug 20 00:26:54 2011

Create temporary tablespace temp tempfile '$ ORACLE_BASE/oradata/test/temp01.dbf' size 100 m

Completed: create temporary tablespace temp tempfile '$ ORACLE_BASE/oradata/test/temp01.dbf' size 100 m

Sat Aug 20 00:26:55 2011

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

Sat Aug 20 00:26:55 2011

ALTER DATABASE DEFAULT TABLESPACE SYSTEM

Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM

Sat Aug 20 00:27:01 2011

SMON: enabling tx recovery

Sat Aug 20 00:27:02 2011

Threshold validation cannot be done before catproc is loaded.

Replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

QMNC started with pid = 13, OS id = 6485

Sat Aug 20 00:27:03 2011

Completed: create database test

User sys identified by ** user system identified by * datafile '$ ORACLE_BASE/oradata/test/system01.dbf' size 400 m

Sysaux datafile '$ ORACLE_BASE/oradata/test/sysaux01.dbf' size 100 m

Undo tablespace rtbs datafile '$ ORACLE_BASE/oradata/test/rtbs01.dbf' size 100 m

Default temporary tablespace temp tempfile '$ ORACLE_BASE/oradata/test/temp01.dbf' size 100 m

Logfile

Group 1 '$ ORACLE_BASE/oradata/test/redo01a. log' size 10 m,

Group 2 '$ ORACLE_BASE/oradata/test/redo02a. log' size 10 m,

Group 3 '$ ORACLE_BASE/oradata/test/redo03a. log' size 10 m

Character set zhs16gbk

5. Create a data dictionary

Data Dictionary script:

[Oracle @ oracle dbs] cat cr_dict. SQL

@ $ ORACLE_HOME/rdbms/admin/catalog. SQL

@ $ ORACLE_HOME/rdbms/admin/catproc. SQL

Conn system/oracle

@ $ ORACLE_HOME/sqlplus/admin/pupbld. SQL

At 11:20: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/cuug/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 ~] $

@ Now, manual database creation is complete!

This article is from the blog of "Tian Ke's blog", please be sure to keep this source http://tiany.blog.51cto.com/513694/1414563