Sample oracle 10g create database script 1

[melsterTEK]

Hello. Can anyone share a standard create database script for oracle 10g? I tried using my old oracle 9i scripts and I am finding there are some gotchas with creating a db in 10gR2. Thanks.

 

[SantaMufasa]

Mel, which operating system (*nix or Windows) ? For my databases, I use traditional rollback segments and a traditional temporary tablespace, so my script will accommodate those, okay?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[melsterTEK]

Hi Mufasa,
This would be for unix. I'm trying to create a db on Linux platform. Thanks.

 

[SantaMufasa]

Mel,

Here are contents of a recent *nix-database-create script:

-- Maintenance History:
-- 2007/01/26 Created and tailored by DHUNT
shutdown abort
startup nomount pfile=/usr/app/oracle/admin/CHI4102/pfile/initCHI4102.ora

create database CHI4102
	controlfile reuse
	logfile group 1 ('/data/oracle/oradata/chidev4/CHI4102log1a.ora',
			 '/data/oracle/oradata/chidev4/CHI4102log1b.ora')
			size 30M reuse,
		group 2 ('/data/oracle/oradata/chidev4/CHI4102log2a.ora',
			 '/data/oracle/oradata/chidev4/CHI4102log2b.ora')
			size 30M reuse,
		group 3 ('/data/oracle/oradata/chidev4/CHI4102log3a.ora',
			 '/data/oracle/oradata/chidev4/CHI4102log3b.ora')
			size 30M reuse
	datafile '/data/oracle/oradata/chidev4/CHI4102system01.dbf' size 80M reuse
                autoextend on next 10m maxsize 2000m
        sysaux datafile '/data/oracle/oradata/chidev4/CHI4102sysaux01.dbf' size 10m reuse
                autoextend on next 10m maxsize 2000m
	character set WE8ISO8859P1;
create tablespace rbs
	datafile '/data/oracle/oradata/chidev4/CHI4102rbs01.dbf' size 10m reuse 
	autoextend on next 10M maxsize 2000m
        segment space management manual
;
alter tablespace rbs add datafile '/data/oracle/oradata/chidev4/CHI4102rbs02.dbf' size 10m reuse
	autoextend on next 10m maxsize 2000m;
alter tablespace rbs add datafile '/data/oracle/oradata/chidev4/CHI4102rbs03.dbf' size 10m reuse
	autoextend on next 10m maxsize 2000m;
create public rollback segment dummy tablespace system;
alter rollback segment dummy online;
create public rollback segment rbs1 tablespace rbs
	storage (initial 1m next 1m optimal 10m maxextents unlimited);
create public rollback segment rbs2 tablespace rbs
	storage (initial 1m next 1m optimal 10m maxextents unlimited);
create public rollback segment rbs3 tablespace rbs
	storage (initial 1m next 1m optimal 10m maxextents unlimited);
create public rollback segment rbs4 tablespace rbs
	storage (initial 1m next 1m optimal 10m maxextents unlimited);
create public rollback segment rbs5 tablespace rbs
	storage (initial 1m next 1m optimal 10m maxextents unlimited);
create public rollback segment rbs6 tablespace rbs
	storage (initial 1m next 1m optimal 10m maxextents unlimited);
alter rollback segment rbs1 online;
alter rollback segment rbs2 online;
alter rollback segment rbs3 online;
alter rollback segment rbs4 online;
alter rollback segment rbs5 online;
alter rollback segment rbs6 online;
alter rollback segment dummy offline;
drop rollback segment dummy;
create tablespace temp temporary
	datafile '/data/oracle/oradata/chidev4/CHI4102temp01.dbf' size 10M reuse
	autoextend on next 10m maxsize 2000m
        segment space management manual
;
alter tablespace system
	default storage (initial 16k next 64k pctincrease 100 maxextents unlimited);
alter tablespace temp
	default storage (initial 16k next 1M pctincrease 50 maxextents unlimited);

@$ORACLE_HOME/rdbms/admin/catalog
@$ORACLE_HOME/rdbms/admin/catproc
@$ORACLE_HOME/rdbms/admin/catblock.sql
@$ORACLE_HOME/rdbms/admin/catexp.sql
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
@$ORACLE_HOME/rdbms/admin/utltkprf.sql
@$ORACLE_HOME/sqlplus/admin/plustrce
connect system/<system password>
@$ORACLE_HOME/sqlplus/admin/pupbld

Let us know if this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[melsterTEK]

Mufasa,
Would you have a sample that configures the SYSAUX tablespace? I think this is new to 10g. Thanks.

 

[SantaMufasa]

Mel,

Notice that the code for defining SYSAUX appears, above, in my code, just above the line that reads: "character set WE8ISO8859P1;". My script looks much better when you copy and paste it to a Notepad file...To try to make the SYSAUX more visible, I've artificially widened the screens on this thread.

Let us know if you have additional questions.
=============================================================================================

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[melsterTEK]

Thanks Mufusa. Would have a sample of your init.ora param settings? This is for a lightweight db. I'm actually creating a repository for rman catalog. So, I would just need barebone settings for a small db. Thank you.

 

[SantaMufasa]

Sure:

# standard init.ora template for Oracle 10g

db_name = "CHI4102"
instance_name = CHI4102
service_names = CHI4102
control_files = ("/dbsrv/oracle/data1/oradata/CHI4102/CHI4102control01a.ctl",
		 "/dbsrv/oracle/data1/oradata/CHI4102/CHI4102control01b.ctl",
                 "/dbsrv/oracle/data1/oradata/CHI4102/CHI4102control02a.ctl",
		 "/dbsrv/oracle/data1/oradata/CHI4102/CHI4102control02b.ctl",
                 "/dbsrv/oracle/data1/oradata/CHI4102/CHI4102control03a.ctl",
		 "/dbsrv/oracle/data1/oradata/CHI4102/CHI4102control03b.ctl")
background_dump_dest = /dbsrv/oracle/app/oracle/admin/CHI4102/bdump
core_dump_dest = /dbsrv/oracle/app/oracle/admin/CHI4102/cdump
user_dump_dest = /dbsrv/oracle/app/oracle/admin/CHI4102/udump
shared_pool_size = 150M
shared_pool_reserved_size = 15M
java_pool_size = 150M
log_buffer = 163840
processes = 1500
db_block_size = 8192
db_block_buffers = 20000
sort_area_retained_size = 64000
sort_area_size = 4096000
db_file_multiblock_read_count = 32
log_checkpoint_interval = 50000
log_checkpoint_timeout = 0
open_cursors = 1024
os_authent_prefix = ""
utl_file_dir = *

I just copied and pasted this initCHI4102.ora file, without editing out any extraneous entries that do not relate to size (e.g., utl_file_dir, os_authent_prefix, et cetera, but, hopefully it gives you a sense for the contents of the file. The size-related parameters (e.g. db_block_buffers, shared_pool_size, et cetera) represent a small-to-medium sized instance (IMHO).

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[melsterTEK]

Hey Mufasa,
Thanks for your help on this. It was very helpful. Are there any other standard scripts that need to be run when using 10G?
For example in oracle 9i, I also had to run these scripts:

JServer.sql
ordinst.sql
interMedia.sql
context.sql
xdb_protocol.sql
spatial.sql
ultraSearch.sql
odm.sql
cwmlite.sql

Thanks,
Mel