Oracle 10 Gb manual database creation method

Oracle 10 Gb manual database creation method generally, the tool that is more convenient to create a database is to use the dbca tool that comes with oracle, which is really very convenient. However, production databases in some industries (such as telecommunications and finance) have the requirement for manual database creation. This is also an important part of the test of DBA technical skills. Manual database creation is also the first level in the OCM examination. As a DBA or quasi-DBA, it is also necessary to master the manual database creation skills of oracle. 1. Preparations before manual database creation 1. Operating System Environment: RHEL6.3 x86_64Bit2.oracle version: oracle10g Release 10.4.03.oacle software: 10201_database_linux_x86_64.cpio + p6810189_10204_Linux-x86-64.zip4. Set environment variables and take effect. Www.2cto.com [root @ oracle10g oracle] # cat/home/oracle/. bash_profile #. bash_profile # Get the aliases and functionsif [-f ~ /. Bashrc]; then .~ /. Bashrcfi # User specific environment and startup programsPATH = $ PATH: $ HOME/binexport PATH # for oracleexport TMP =/tmpexport TMPDIR = $ TMPexport ORACLE_BASE =/u01/oracleexport ORACLE_HOME =/u01/oracle/product/10.2.0/db_1export ORACLE_SID = ORCLexport ORACLE_TERM = xtermexico Port PATH =/usr/sbin: $ PATHexport PATH = $ ORACLE_HOME/bin: $ PATHexport LD_LIBRARY_PATH = $ ORACLE_HOME/lib:/usr/libexport CLASSPATH = $ ORACLE_HOM E/jre: $ ORACLE_HOME/jlib: $ ORACLE_HOME/rdbms/jlibexport EDITOR = vi www.2cto.com export NLS_LANG = export NLS_DATE_FORMAT = 'yyyy-MM-DD HH24: MI: SS [oracle @ oracle10g ~] $ Source/home/oracle /. bash_profile5. planned tablespace and data file path and size: system tablespace size: 350 MB; sysaux tablespace: 300 MB; temp temporary tablespace: 100 MB; undo tablespace: 200 MB; users tablespace: 100 MB. Create the storage path for related files [oracle @ oracle10g u01] $ mkdir-p/u01/oracle/oradata/ORCL [oracle @ oracle10g u01] $ mkdir-p/u01/oracle/flash_recovery_area [oracle @ oracle10g u01] $ mkdir-p/u01/oracle/admin/ORCL/{adump, bdump, cdump, udump} 2. manual database creation process 1. set the ORACLE_SID environment variable [oracle @ oracle10g u01] $ export ORACLE_SID = ORCL2. modify the oratab file as follows [oracle @ oracle10g etc] $ cat oratab # This file is used by ORACLE utilities. it is created by root. sh # and updated by the Database Configuration Assistant when creating # a database. # A colon, ':', is used as the field terminator. A new line terminates # the entry. lines beginning with a pound sign, '#', are comments. # Entries are of the form: # $ ORACLE_SID: $ ORACLE_HOME: <N | Y>: # www.2cto.com # The first and second fields are the system identifier and home # directory of the database respectively. the third filed indicates # to the dbstart utility that the database shocould, "Y", or shocould not, # "N", be brought up at system boot time. # Multiple entries with the same $ ORACLE_SID are not allowed. # Add the following content: $ ORACLE_SID: $ ORACLE_HOME: <N | Y>: ORCL:/u01/oracle/product/10.2.0/db_1: Y3. create the orapwd remote login password file, path:/u01/oracle/product/10.2.0/db_1/dbs [oracle @ oracle10g dbs] $ orapwdUsage: orapwd file = <fname> password = <password> entries = <users> force = <y/n> nosysdba = <y/n> where file-name of password file (mand), password-password for SYS (mand), entries-maximum number of distinct DBA, force-whether to overwrite existing file (opt ), nosysdba-whether to shut out the SYSDBA logon (opt for Database Vault only ). there are no spaces around the equal-to (=) character. [oracle @ oracle10g dbs] $ orapwd file = orapwORCL password = 'sys 'entries = 5 [oracle @ oracle10g dbs] $ lltotal 52-rw-r -- r -- 1 oracle oinstall 12920 May 3 2001 initdw. ora-rw-r ----- 1 oracle oinstall 8385 Sep 11 1998 init. ora -- rw-r ----- 1 oracle oinstall 2048 Aug 9 orapwORCL
4. create a pfile. You can create or copy another pfile File Based on the template and modify [root @ oracle10g dbs] # cat initORCL. ora ORCL. _ db_cache_size = 58720256ORCL. _ java_pool_size = 4192134orcl. _ large_pool_size = 4192134orcl. _ shared_pool_size = 92274688ORCL. _ streams_pool_size = 0 *. audit_file_dest = '/u01/oracle/admin/ORCL/adump '*. background_dump_dest = '/u01/oracle/admin/ORCL/bdump '*. compatible = '10. 2.0.3.0 '*. control_files = '/u01/oracle/oradata/ORCL/control01.ctl', '/u01/oracle/oradata/ORCL/control02.ctl ', '/u01/oracle/oradata/ORCL/control03.ctl '*. core_dump_dest = '/u01/oracle/admin/ORCL/cdump '*. db_block_size = 8192 www.2cto.com *. db_domain = ''*. db_file_multiblock_read_count = 16 *. db_name = 'orcl '*. db_recovery_file_dest = '/u01/oracle/flash_recovery_area '*. db_recovery_file_dest_size = 2147483648 *. dispatchers = '(PROTOCOL = TCP) (SERVICE = ORCLXDB )'*. job_queue_processes = 10 *. open_cursors = 300 *. pga_aggregate_target = 52428800 *. processes = 150 *. remote_login_passwordfile = 'clusive '*. sga_max_size = 314572800 *. sga_target = 167772160 *. shared_servers = 1 *. undo_management = 'auto '*. undo_retention = 1800 *. undo_tablespace = 'undotbs1 '*. user_dump_dest = '/u01/oracle/admin/ORCL/udump'
5. Compile the database creation script. For details, refer to the example in the oracle10g Official Document [oracle @ oracle10g ~]. $ Cat createdb. SQL CREATE DATABASE ORCL USER SYS IDENTIFIED BY "sys" USER SYSTEM IDENTIFIED BY "sys" LOGFILE GROUP 1 ('/u01/oracle/oradata/ORCL/redo01_1.log ', '/u01/oracle/oradata/ORCL/redo01_2.log') SIZE 50 M, GROUP 2 ('/u01/oracle/oradata/ORCL/redo02_1.log ', '/u01/oracle/oradata/ORCL/redo02_2.log') SIZE 50 M, GROUP 3 ('/u01/oracle/oradata/ORCL/redo03_1.log ', '/u01/oracle/oradata/ORCL/redo03_2.log ') SIZE 50 m maxlogfiles 10 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 character set character national character set character datafile '/u01/oracle/oradata/ORCL/system01.dbf 'size 350 M reextuse ent MANAGEMENT LOCAL www.2cto.com sysaux datafile '/u01/oracle/oradata/ORCL/comment 'size 300 m reuse default temporary tablespace temp tempfile'/u01/oracle/oradata/ORCL/temp01.dbf' SIZE 100 M REUSE autoextend on next 10 m maxsize 32767 m undo tablespace UNDOTBS1 DATAFILE '/u01/oracle/oradata/ORCL/undotbs01.dbf' SIZE 200 m reuse autoextend on next 10 m maxsize unlimited;
6. Run the database creation script to create a database. To create a database, you must have SYSDBA system permissions. In this example, you can use SYS to create a database. [Oracle @ oracle10g dbs] $ sqlplus/as sysdba SQL * Plus: Release 10.2.0.4.0-Production on Thu Aug 9 17:36:51 2012 Copyright (c) 1982,200 7, Oracle. all Rights Reserved. connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup nomountORACLE instance started. total System Global Area 314572800 bytesFixed Size 2083656 bytesVariable Size 247465144 bytesDatabase Buffers 58720256 bytesRedo Buffers 6303744 bytesSQL> @ createdb. sqlDatabase created.
7. after the database is created successfully, check whether the related data file is successfully created [root @ oracle10g ORCL] # ll/u01/oracle/oradata/ORCL/total 1197936-rw-r ----- 1 oracle oinstall 6897664 Aug 9 control01.ctl-rw-r ----- 1 oracle oinstall 6897664 Aug 9 control02.ctl-rw-r ----- 1 oracle oinstall 6897664 Aug 9 control03.ctl-rw-r ----- 1 oracle oinstall 52429312 Aug 9 redo01_1.log-rw-r ----- 1 oracle oinstall 52429312 aug 9 redo01_2.log-rw-r ----- 1 oracle oinstall 52429312 Aug 9 redo02_1.log-rw-r ----- 1 oracle oinstall 52429312 Aug 9 redo02_2.log-rw-r ----- 1 oracle oinstall 52429312 Aug 9 redo03_1.log-rw-r ----- 1 oracle oinstall 52429312 Aug 9 redo03_2.log-rw-r ----- 1 oracle oinstall 314580992 Aug 9 sysaux01.dbf-rw-r ----- 1 oracle oinstall 367009792 Aug 9 system01.dbf-rw-r ----- 1 oracle oinstall 104865792 Aug 9 temp01.dbf-rw-r ----- 1 oracle oinstall 209723392 Aug 9 undotbs01.dbf
8. CREATE user TABLESPACE usersSQL> create tablespace "USERS" DATAFILE '/u01/oracle/oradata/ORCL/users01.dbf' SIZE 100 m autoextend on next 1310720 MAXSIZE 32767 m extent management local uniform size 10 M; tablespace created. www.2cto.com 9. modify the users tablespace to the user's default tablespace SQL> alter database default tablespace users; Database altered.10. run the script, rebuilding the data dictionary view SQL> @/u01/oracle/product/10.2.0/db_1/rdbms/admin/catalog. sqlSQL> @/u01/oracle/product/10.2.0/db_1/rdbms/admin/catproc. sqlSQL> @ // u01/oracle/product/10.2.0/db_1/sqlplus/admin/pupbld. SQL if initORCL. the parameter set in the ora file is not *. compatible = '10. 2.0.3.0 ', run the following command to upgrade the database SQL> startup upgradeSQL> @/u01/oracle/product/10.2.0/db_1/rdbms/admin/catupgrd. SQL
11. set listener [oracle @ oracle10g admin] $ cat listener. ora # listener. ora Network Configuration File:/u01/oracle/product/10.2.0/db_1/network/admin/listener. ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME =/u01/oracle/product/10.2.0/db_1) (PROGRAM = extproc ))) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = oracle10g.cluster.com) (PORT = 1521) (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0 ))))
Iii. Test Database 1. create testSQL> create user test identified by test; User created. SQL> grant connect, resource to test; Grant succeeded. SQL> select default_tablespace, temporary_tablespace from dba_users where username = 'test'; DEFAULT_TABLESPACE TEMPORARY_TABLESPACE -------------------------- using USERS TEMPSQL> conn TEST/testConnected. SQL> show userUSER is "TEST" SQL> create table test (id number); Table created. www.2cto.com SQL> insert into test values (1); 1 row created. SQL> select id from test; ID ---------- 12. use the rman tool to back up the database [oracle @ oracle10g dbs] $ rman target/Recovery Manager: Release 10.2.0.4.0-Production on Tue Aug 21 15:50:34 2012 Copyright (c) 1982,200 7, Oracle. all rights reserved. connected to target database: ORCL (DBID = 1319428512) RMAN> backup database; Starting backup at 2012-08-21 15: 53: 55 using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid = 142 devtype = DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile (s) in backupsetinput datafile fno = 00001 name =/u01/oracle/oradata/ORCL/system01.dbfinput datafile fno = 00003 name =/u01/oracle/oradata/ORCL/sysaux01.dbfinput datafile fno = 00002 name /u01/oracle/oradata/ORCL/undotbs01.dbfinput datafile fno = 00004 name =/u01/oracle/oradata/ORCL/users01.dbfchannel ORA_DISK_1: starting piece 1 at 2012-08-21 15: 53: 58 channel ORA_DISK_1: finished piece 1 at 2012-08-21 15: 55: 13 piece
Handle =/u01/oracle/flash_recovery_area/ORCL/backupset/2012_08_21/o1_mf_nnndf_TAG20120821T155357_836hnp7g _. bkp tag = TAG20120821T155357 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 01: 16 channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile (s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchanne L ORA_DISK_1: starting piece 1 at 2012-08-21 15: 55: 15 channel ORA_DISK_1: finished piece 1 at 2012-08-21 15: 55: 18 piece www.2cto.com handle =/u01/oracle/flash_recovery_area/ORCL/backupset/2012_08_21/o1_mf_ncsnf_TAG20120821T155357_836hq3vw _. bkp tag = TAG20120821T155357 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 05 Finished backup at 2012-08-21 15: 55: 18 RMAN> quit Recovery Man Ager complete. 4. By learning and mastering the oracle10g manual database creation method, you can gain a deeper understanding of the oracle architecture; mastering related skills makes daily database management very easy.