10g Manual Database Creation in Oracle (Single Instance and RAC) (文档 ID 240052.1)

10g Manual Database Creation in Oracle (Single Instance and RAC) (文档 ID 240052.1)

转到底部

---

PURPOSE ------- The purpose of this bulletin is to give an example of a manual database creation in 10g. MANDATORY ========== FROM 10g RELEASE WE HAVE SYSAUX TABLESPACE MANDATORY FOR STATISTICS WORKLOAD REPOSITORY FACILITIES (SWRF) GOOD PRACTICE ============= CREATE DEFAULT TABLESPACE, WHILE CREATING THE DATABASE. So whenever DBA will create a new user it will, use the default permanent tablespace, unless DBA is mentioning the DEFAULT TABLESPACE clause while creating the user. To use default tablespace option, it is mandatory to use the init.ora parameter "Compatible must be >=10.0" SCOPE & APPLICATION ------------------- Oracle recommends using the Database Configuration Assistant (DBCA) to create your database. These steps are available for DBAs who want to manually create a 10g database either in single instance or Real Application Clusters mode. Tips to create a database in 10g Single Instance or Real Application Clusters. ----------------------------------------------------------------------------- Manual Database Creation steps for Single-Instance. ===================================================== Here are the steps to be followed: 1. Make a init<SID>.ora in your $ORACLE_HOME/dbs directory. On Windows this file is in $ORACLE_HOME\database. To simplify, you can copy init.ora to init<SID>.ora and modify the file. *** Path names, file names, and sizes will need to be modified Example parameter settings : db_block_size=8192 db_cache_size=52428800 background_dump_dest=/u01/32bit/app/oracle/product/9.0.1/rdbms/log core_dump_dest=/u01/32bit/app/oracle/product/9.0.1/rdbms/log user_dump_dest=/u01/32bit/app/oracle/product/9.0.1/rdbms/log timed_statistics=TRUE control_files=("/u01/rbdb1/control_01.ctl", "/u01/rbdb1/control_02.ctl") db_name=rbdb1 shared_pool_size=524288000 sort_area_size=524288 undo_management=AUTO undo_tablespace=UNDOTBS * The local_listener parameter requires that you first add the listener address to the TNSNAMES.ORA - remember to do so on both Node 1 and Node 2. ** You can also use an spfile as described in Note 162491.1. 2. Run the following sqlplus command to connect to the database: sqlplus '/ as sysdba' 3. Startup up the database in NOMOUNT mode: SQL> startup nomount 4. Create the Database : *** Path names, file names, and sizes will need to be modified CREATE DATABASE <db_name> MAXLOGFILES 255 MAXINSTANCES 1 MAXDATAFILES 256 MAXLOGHISTORY 256 DATAFILE '/u01/oracle/rbdb1/system_01.dbf' SIZE 900M REUSE EXTENT MANAGEMENT LOCAL segment space management auto UNDO TABLESPACE "UNDOTBS" DATAFILE ‘/u01/oracle/rbdb1/undotbs_01.dbf' SIZE 200M REUSE EXTENT MANAGEMENT LOCAL segment space management auto DEFAULT TABLESPACE USER_DEFAULT DATAFILE '/u01/oracle/rbdb1/user_default_1.dbf' size 2000m REUSE SYSAUX DATAFILE '/u01/oracle/rbdb1/sysaux_1.dbf' size 500M REUSE segment space management auto CHARACTER SET US7ASCII LOGFILE GROUP 1 ('/u01/oracle/rbdb1/redo1_01.dbf') SIZE 100M REUSE, GROUP 2 ('/u01/oracle/rbdb1/redo1_02.dbf') SIZE 100M REUSE; 5. Create a Temporary Tablespace : *** Path names, file names, and sizes will need to be modified CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u01/oracle/rbdb1/temp_01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"; 6. Run the scripts necessary to build views, synonyms, etc. : The primary scripts that you must run are: i> CATALOG.SQL-- creates the views of data dictionary tables and the dynamic performance views. ii> CATPROC.SQL-- establishes the usage of PL/SQL functionality and creates many of the PL/SQL Oracle supplied packages. ============================================================== Manual Database Creation steps for Real Application Clusters ============================================================== Here are the steps to be followed to create a Real Application Clusters database: 1. Make a init<SID>.ora in your $ORACLE_HOME/dbs directory. On Windows this file is in $ORACLE_HOME\database. To simplify, you can copy init.ora to init<SID>.ora and modify the file. Remember that your control file must be pointing to a pre-existing raw device or cluster file system location. *** Path names, file names, and sizes will need to be modified Example parameter settings for the first instance: Cluster-Wide Parameters for Database "RAC": db_block_size=8192 db_cache_size=52428800 background_dump_dest=/u01/32bit/app/oracle/product/9.0.1/rdbms/log core_dump_dest=/u01/32bit/app/oracle/product/9.0.1/rdbms/log user_dump_dest=/u01/32bit/app/oracle/product/9.0.1/rdbms/log timed_statistics=TRUE control_files=("/dev/RAC/control_01.ctl", "/dev/RAC/control_02.ctl") db_name=RAC shared_pool_size=524288000 sort_area_size=524288 undo_management=AUTO #cluster_database=true /* Uncomment the parameter, when database created */ cluster_database_instances=2 remote_listener=LISTENERS_RAC Instance Specific Parameters for Instance "RAC1": instance_name=RAC1 instance_number=1 local_listener=LISTENER_RAC1 thread=1 undo_tablespace=UNDOTBS * The local_listener parameter requires that you first add the listener address to the TNSNAMES.ORA - remember to do so on both Node 1 and Node 2. ** You can also use an spfile as described in Note 136327.1. 2. Run the following sqlplus command to connect to the database: sqlplus '/ as sysdba' 3. Startup up the database in NOMOUNT mode: SQL> startup nomount 4. Create the Database (All raw devices must be pre-created) : *** Path names, file names, and sizes will need to be modified CREATE DATABASE <db_name> CONTROLFILE REUSE MAXDATAFILES 254 MAXINSTANCES 32 MAXLOGHISTORY 100 MAXLOGMEMBERS 5 MAXLOGFILES 64 DATAFILE '/dev/RAC/system_01_400.dbf' SIZE 900M REUSE EXTENT MANAGEMENT LOCAL segment space management auto UNDO TABLESPACE "UNDOTBS" DATAFILE '/dev/RAC/undotbs_01_210.dbf' SIZE 200M REUSE EXTENT MANAGEMENT LOCAL segment space management auto DEFAULT TABLESPACE USER_DEFAULT DATAFILE '/u01/oracle/rbdb1/user_default_1.dbf' size 2000M REUSE SYSAUX DATAFILE '/u01/oracle/rbdb1/sysaux_1.dbf' size 500M REUSE EXTENT MANAGEMENT LOCAL segment space management auto CHARACTER SET US7ASCII LOGFILE GROUP 1 ('/dev/RAC/redo1_01_100.dbf') SIZE 100M REUSE, GROUP 2 ('/dev/RAC/redo1_02_100.dbf') SIZE 100M REUSE; 5. Create a Temporary Tablespace: *** Path names, file names, and sizes will need to be modified CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/dev/RAC/temp_01_50.dbf' SIZE 40M REUSE 6. Create a 2nd Undo Tablespace: *** Path names, file names, and sizes will need to be modified CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/dev/RAC/undotbs_02_210.dbf' SIZE 200M REUSE NEXT 5120K MAXSIZE UNLIMITED; 7. Run the necessary scripts to build views, synonyms, etc.: The primary scripts that you must run are: i> CATALOG.SQL--creates the views of data dictionary tables and the dynamic performance views ii> CATPROC.SQL--establishes the usage of PL/SQL functionality and creates many of the PL/SQL Oracle supplied packages iii> CATCLUST.SQL--creates RAC specific views 8. Edit init<SID>.ora and set appropriate values for the 2nd instance on the 2nd Node: *** Names may need to be modified instance_name=RAC2 instance_number=2 local_listener=LISTENER_RAC2 thread=2 undo_tablespace=UNDOTBS2 9. From the first instance, run the following command: *** Path names, file names, and sizes will need to be modified alter database add logfile thread 2 group 3 ('/dev/RAC/redo2_01_100.dbf') size 100M, group 4 ('/dev/RAC/redo2_02_100.dbf') size 100M; alter database enable public thread 2;