We are going to create a 10g database for this example, and the first step is to create a Pfile for this database and place it under $ORACLE_HOME/dbs; you have to name it initDBNAME.ora where DBNAME is the name of your database. If you need Pfile examples you can check this example for 10g and also this example for 11g.
Next we need to create some directories for the database (for 11g just $ORACLE_BASE/oradata/example):
oracle@myserver:~$ mkdir -p $ORACLE_HOME/admin/example/bdump
oracle@myserver:~$ mkdir $ORACLE_HOME/admin/example/cdump
oracle@myserver:~$ mkdir $ORACLE_HOME/admin/example/udump
oracle@myserver:~$ mkdir $ORACLE_HOME/oradata/example
Before creating the database we also need to set environment variables:
oracle@myserver:~$ export ORACLE_SID=example
oracle@myserver:~$ set|grep ORA
ORACLE_BASE=/opt/oracle/app/oracle
ORACLE_HOME=/opt/oracle/app/oracle/product/10.2.0/db_1
ORACLE_SID=example
Now we start the new database in nomount mode and create it:
oracle@myserver:~$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 13 15:11:12 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 88081640 bytes
Database Buffers 75497472 bytes
Redo Buffers 2920448 bytes
SQL> CREATE DATABASE example
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGFILES 10
MAXLOGMEMBERS 4
DATAFILE
'?/oradata/example/system01.dbf' size 512M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '?/oradata/example/temp01.dbf' size 256M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
LOGFILE
GROUP 1 (
'?/oradata/example/redo01a.rdo',
'?/oradata/example/redo01b.rdo') size 64M,
GROUP 2 (
'?/oradata/example/redo02a.rdo',
'?/oradata/example/redo02b.rdo') size 64M,
GROUP 3 (
'?/oradata/example/redo03a.rdo',
'?/oradata/example/redo03b.rdo') size 64M
SYSAUX
DATAFILE '?/oradata/example/sysaux01.dbf' size 512M
UNDO TABLESPACE UNDO
DATAFILE '?/oradata/example/undo01.dbf' size 512M
/
Database created.
Of course you have to set file names that match the oradata directory created, modify sizes of files if they are too small, place files in proper places like redo logs in different filesystems, and finally modify or add parameters according to your specific needs.
Next we create the USERS tablespace and the database catalog:
SQL> CREATE TABLESPACE USERS LOGGING DATAFILE
'?/oradata/example/users.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE 512M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> @?/rdbms/admin/catalog
...
Grant succeeded.
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/catproc
...
1 row created.
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/catexp
...
0 rows created.
Commit complete.
SQL> @?/rdbms/admin/catblock
...
Synonym created.
Grant succeeded.
SQL> connect system/manager
Connected.
SQL> @?/sqlplus/admin/pupbld
...
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
SQL> @?/rdbms/admin/catdbsyn
...
Synonym created.
Synonym created.
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@myserver:~$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 13 15:39:32 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/utlrp.sql
..
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Your new database is almost complete, you just have to create an SPfile and check installed components:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 88081640 bytes
Database Buffers 75497472 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
SQL> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 88081640 bytes
Database Buffers 75497472 bytes
Redo Buffers 2920448 bytes
Database mounted.
Database opened.
SQL> set pagesize 9999
SQL> col comp_id for a10
SQL> col comp_name for a40
SQL> col version for a15
SQL> column status format a10
SQL> select comp_id, comp_name, version, status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- ----------
CATALOG Oracle Database Catalog Views 10.2.0.5.0 VALID
CATPROC Oracle Database Packages and Types 10.2.0.5.0 VALID
And if you like, you can configure PERFSTATS this way:
SQL> CREATE TABLESPACE PERFSTAT DATAFILE
'?/oradata/example/perfstat.dbf' SIZE 192M AUTOEXTEND ON NEXT 16M MAXSIZE 512M
NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
Tablespace created.
SQL> define default_tablespace='PERFSTAT'
SQL> define temporary_tablespace='TEMP'
SQL> define perfstat_password='perfstat'
SQL> @?/rdbms/admin/spcreate.sql
...
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
That's all! You have a minimum brand-new database ready to use; just don't forget adding your database to the oratab file, creating a listener for this database, changing default passwords and disabling default accounts, and doing any administrative thing like these required.
No comments:
Post a Comment