In certain circumstances a DBA should be able to create an Oracle database without the help of graphics utilities available in Oracle (DBCA).
is good to experiment with the manual creation of Oracle databases, as it delivers an experience that allows and decant understand many concepts about the functioning of the Oracle engine.
Steps to create an Oracle database: Decide
unique name for the instance, database name, Oracle block size, character set, maximum number of data files, and maximum number of files to redo. Decide
physical storage structure of the database (ASM, File System, Raw Devices). In the example uses storage by file system, and have defined and created the mount points according to Oracle OFA model.
Copy and edit the parameter file (init.ora) that allows you to initialize the Oracle instance.
Set appropriate operating system variables (ORACLE_SID), other variables such as ORACLE_HOME, ORACLE_BASE should be predefined.
Create password file (depending on the value that is defined for the parameter REMOTE_LOGIN_PASSWORDFILE)
Invoking SQLPLUS and connect to the database as sysdba. Start the instance
NOMOUNT state. This statement creates a new database.
Create the database (execute script creation the database)
Example parameter file *. inittest.ora
audit_file_dest = '/ u01/app/oracle/admin/test/adump' *.
background_dump_dest = '/ u01/app / oracle / admin / test / bdump '
*. compatible = '10 .2.0.1.0' *.
control_files = '/ u02/oradata/test/control01.ctl', '/ u02/oradata/test/control02. ctl ',' / u02/oradata/test/control03.ctl '*.
core_dump_dest =' / u01/app/oracle/admin/test/cdump '
db_block_size = 8192 *. *.
db_domain =' midominio.cl '
db_file_multiblock_read_count = 16 *.
*. db_name = 'test' *.
db_recovery_file_dest = '/ u01/app/oracle/flash_recovery_area' *.
db_recovery_file_dest_size = 2147483648 *. dispatchers = '(PROTOCOL = TCP) (SERVICE = testXDB)' *. job_queue_processes
= 10 *.
open_cursors = 300 = 92274688
pga_aggregate_target *. *. *. PROCESSES = 150
remote_login_passwordfile = 'EXCLUSIVE' *.
sga_target = 277872640 *. UNDO_MANAGEMENT = 'AUTO' *.
undo_tablespace = 'TS_UNDO' *.
user_dump_dest = '/ u01/app/oracle/admin/test/udump'
Creation Example Manual Oracle Database 9i
In the examples that follow has been prepared called inittest.ora file containing the parameters of the instance named test.
u01/app/oracle/database/oracle9iR2/dbs> sqlplus "/ as sysdba"
SQL * Plus: Release 9.2.0.2.0 - Production on Wed April 5
14:08:37 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup pfile = nomount? / Dbs / inittest.ora
ORACLE instance started.
Total System Global Area 160925320 bytes Fixed Size 730760 bytes
Variable Size 109051904 bytes
Database Buffers 50331648 bytes
Redo Buffers 811008 bytes
SQL> CREATE DATABASE test
LOGFILE group 1 (’/u01/oradata/test/redolog1a.dbf’,
‘/u02/oradata/test/redolog1b.dbf’ ) SIZE 10M,
group 2 (’/u01/oradata/test/redolog2a.dbf’,
‘/u02/oradata/test/redolog2b.dbf’ ) SIZE 10M,
group 3 (’/u01/oradata/test/redolog3a.dbf’,
‘/u02/oradata /test/redolog3b.dbf’ ) SIZE 10M
DATAFILE ‘/u02/oradata/test/system01.dbf’ SIZE 200M
CHARACTER SET WE8ISO8859P1 National character set utf8
EXTENT MANAGEMENT LOCAL ts_undo
undo tablespace datafile
'/ u02/oradata/test/undo01.dbf'
size 50M default temporary tablespace tempfile ts_temp
If an error ORA-01031: insufficient privileges, that means most likely, is that the current user is in dba group (on unix), or group ORA_DBA (Windows). If the init.ora file is not in default location or has not been found with the pfile attribute, an error ORA-01078: failure in processing system parameters and it throws an error LRM-00109: could not open parameter file '/ u01/app/oracle/product/database/9.2.0/db_1/dbs/inittest.ora'
Creation Example Manual Oracle Database 10g
/ u01/app/oracle/product/database/10.2.0/db_1/dbs> sqlplus "/ as sysdba" SQL * Plus: Release 10.2.0.1.0 - Production on Wed April 2005 14:08: 37 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup pfile = nomount? / Dbs / inittest.ora
ORACLE instance started.
Total System Global Area 160925320 bytes
Fixed Size 730760 bytes
Variable Size 109051904 bytes
Database Buffers 50331648 bytes
Redo Buffers 811008 bytes
SQL> CREATE DATABASE test
‘/u02/oradata/test/redolog1b.dbf’ ) SIZE 10M, group 2 (’/u01/oradata/test/redolog2a.dbf’, ‘/u02/oradata/test/redolog2b.dbf’ ) SIZE 10M,
group 3 (’/u01/oradata/test/redolog3a.dbf’, ‘/u02/oradata /test/redolog3b.dbf’ ) SIZE 10M
DATAFILE ‘/u02/oradata/test/system01.dbf’ SIZE 200M CHARACTER SET WE8ISO8859P1 national character set utf8
EXTENT MANAGEMENT LOCAL SYSAUX datafile
'/ u02/oradata/test/sysaux01.dbf' size 300M
autoextend on next 10M maxsize unlimited undo tablespace datafile ts_undo
'/ u02/oradata/test/undo01.dbf 'size 50M default temporary tablespace tempfile ts_temp
' / u02/oradata/test/temp01.dbf 'size 50M
autoextend on next 50M maxsize 300M;
create database command also executes a file whose name is determined by the start parameter (hidden) _init_sql_file. After creating the database, it can be mounted and opened for use.
After creating a raw database, you must complete some additional tasks: Tasks
post
creation of the database
Once you have created the database to create database command, it must be supplemented by running some scripts to create the catalog and procedural option that allows you to execute PL / SQL. Run as SYS
·? / Rdbms / admin / catalog.sql
·? / Rdbms / admin / catproc.sql and
Where? represents a shortcut to the value of the $ ORACLE_HOME environment variable (only in sqlplus)
catalog.sql flame for example, catexp.sql which is a requirement for the exp utility for creating backups or dbmsstdx.sql logic which is a requirement to create triggers.
You can also run system? / Sqlplus / admin / pupbld.sql. pupbld.sql creates a table that allows you to block someone else to use sqlplus.
0 comments:
Post a Comment