Oracle 10g and Oracle 11g hand-built case--oracle 10g

Oracle 10g and Oracle 11g hand-built case--oracle 10g

System environment:

Operating system: RedHat EL6

Oracle:oracle 10g and Oracle 11g

The Manual construction of the library is relatively easy to implement, this case is from 10g and 11g, by hand to build a library to do a simple control, can see 11g and 10g a simple difference!

Case one:

Build libraries manually in Oracle 10g environments

Db_name= ' Cuug '

Instance_name= ' Cuug '

1, establish instance initialization parameter file and password file:

[email protected] dbs]$ cat Initcuug.ora

Db_name = Cuug

Db_block_size = 8192

Pga_aggregate_target = 30M

Db_cache_size = 80M

Shared_pool_size = 60M

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

[[email protected] DBS] $orapwd file=orapwcuug password=oracle entries=3

2. Set up Database related folders

[Email protected] dbs]$ mkdir-p $ORACLE _base/admin/cuug/bdump

[Email protected] dbs]$ mkdir-p $ORACLE _base/admin/cuug/cdump

[Email protected] dbs]$ mkdir-p $ORACLE _base/admin/cuug/udump

[Email protected] dbs]$ mkdir-p $ORACLE _base/oradata/cuug

3. Build a library script (for Oracle 11g Online)

[[email protected] 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 300m

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

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

Undo tablespace Rtbs DataFile ' $ORACLE _base/oradata/cuug/rtbs01.dbf ' size 100m

LogFile

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

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

Character Set ZHS16GBK;

4. Start instance to set up the database

[[email protected] DBS] $export oracle_sid=cuug

[[email protected] dbs] $sqlplus '/as sysdba '

10:59:59 [email protected] test1>startup nomount;

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 [email protected] Test1>@/home/oracle/cr_db.sql

Database created.

View Alarm log information (alert_cuug.log);

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

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

Default storage (initial 10K next 10K) online

Sat 20 00:26:34 2011

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

Default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY Online

Sat 20 00:26:34 2011

Create rollback segment System tablespace system

Storage (initial 50K next 50K)

Completed:create Rollback segment System tablespace system

Storage (initial 50K next 50K)

Sat 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 20 00:26:50 2011

CREATE UNDO tablespace rtbs datafile ' $ORACLE _base/oradata/test/rtbs01.dbf ' size 100m

Sat 20 00:26:51 2011

Successfully onlined Undo tablespace 1.

Completed:create UNDO tablespace rtbs datafile ' $ORACLE _base/oradata/test/rtbs01.dbf ' size 100m

Sat 20 00:26:51 2011

Create tablespace sysaux datafile ' $ORACLE _base/oradata/test/sysaux01.dbf ' size 100m

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO Online

Completed:create tablespace sysaux datafile ' $ORACLE _base/oradata/test/sysaux01.dbf ' size 100m

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO Online

Sat 20 00:26:54 2011

CREATE temporary tablespace TEMP tempfile ' $ORACLE _base/oradata/test/temp01.dbf ' size 100m

Completed:create temporary tablespace TEMP tempfile ' $ORACLE _base/oradata/test/temp01.dbf ' size 100m

Sat 20 00:26:55 2011

ALTER DATABASE DEFAULT Temporary tablespace TEMP

Completed:alter DATABASE DEFAULT Temporary tablespace TEMP

Sat 20 00:26:55 2011

ALTER DATABASE DEFAULT tablespace SYSTEM

Completed:alter DATABASE DEFAULT tablespace SYSTEM

Sat 20 00:27:01 2011

smon:enabling TX Recovery

Sat 20 00:27:02 2011

Threshold validation cannot be do 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 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 400m

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

Undo tablespace Rtbs DataFile ' $ORACLE _base/oradata/test/rtbs01.dbf ' size 100m

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

LogFile

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

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

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

Character Set ZHS16GBK

5. Create a data dictionary

Data Dictionary script:

[Email protected] 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

11:20:12 [email protected] Test1>@/home/oracle/cr_dict.sql

6. Set up the Users table space and set it as the default tablespace

The default tablespace is System, which assumes that system is the default tablespace and affects the management and performance of the database.

12:09:56 sql> Create tablespace users

12:10:01 2 datafile '/u01/app/oracle/oradata/cuug/user01.dbf ' size 100m;

Tablespace created.

12:10:41 sql>ALTER DATABASE default tablespace users;

Database altered.

7. Join the example case

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

[Email protected] ~]$

@ Now, hand-built library finished!

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1414563