Create a new Oracle instance without using the installer?
Problem
I have already installed the Oracle server but don’t want to use the Oracle installer’s trial database. I want to create a new Oracle instance from scratch.Technique
The Oracle Instance Manager automates some of the tasks required to set up a new Oracle database instance. The starting point of the process is a new parameter initialization file. When this is available, the Instance Manager will create a new instance and run the scripts that build the data dictionary.Steps
Create a new parameter initialization file with any ASCII editor. Start the Oracle Instance Manager and create the new Oracle instance, specifying the locations of the new parameter file and the other parts of the physical database layer as necessary. After instance creation, allow Enterprise Manager and SQL*Plus sessions to access the new instance by using network configuration manager to set up a new service. You will create an instance called O8HT to illustrate this process.1. Create a new copy of the parameter initialization file. Under Windows NT, the Oracle8 installer placed a sample parameter initialization file in the \ORANT\DATABASE subdirectory and named it INIT<DATABASE SID>.ORA. Alternatively, the sample parameter initialization file in Listing 1.1 will suffice as a starting point. If you let the installer create a trial database instance, you can safely take the default for all the parameters except db_name and control_files; you can modify the other parameters later based on database performance history. The initialization parameter file in Listing 1.1 specifies multiplexed control files named CTL1O8HT.ORA and CTL2O8HT.ORA (learn more about multiplexing, or mirroring, control files in How-To 1.4). Lines beginning with the # symbol in Listing 1.1 are commented out; Oracle8 ignores them during instance startup.
Listing 1.1 A sample parameter initialization file db_name = o8ht db_files = 1024 control_files = (C:\ORANT\DATABASE\ctl1o8ht.ora, C:\ORANT\DATABASE\mirror\ctl2o8ht.ora) db_file_multiblock_read_count=8 ..........# INITIAL # db_file_multiblock_read_count=8 ..........# SMALL # db_file_multiblock_read_count=16 ..........# MEDIUM # db_file_multiblock_read_count=32 ..........# LARGE db_block_buffers=200 ..........# INITIAL # db_block_buffers=200 ..........# SMALL # db_block_buffers = 550 ..........# MEDIUM # db_block_buffers = 3200 ..........# LARGE # shared_pool_size = 6500000 ..........# INITIAL shared_pool_size = 3500000 ..........# SMALL # shared_pool_size = 6000000 ..........# MEDIUM # shared_pool_size = 9000000 ..........# LARGE log_checkpoint_interval = 10000 processes = 50 ..........# INITIAL # processes = 50 ..........# SMALL # processes = 100 ..........# MEDIUM # processes = 200 ..........# LARGE dml_locks = 100 ..........# INITIAL # dml_locks = 100 ..........# SMALL # dml_locks = 200 ..........# MEDIUM # dml_locks = 500 ..........# LARGE log_buffer = 8192 ..........# INITIAL # log_buffer = 8192 .......... # SMALL # log_buffer = 32768 ..........# MEDIUM # log_buffer = 163840 ..........# LARGE sequence_cache_entries = 10 ..........# INITIAL # sequence_cache_entries = 10 ..........# SMALL # sequence_cache_entries = 30 ..........# MEDIUM # sequence_cache_entries = 100 ..........# LARGE sequence_cache_hash_buckets = 10 ..........#INITIAL # sequence_cache_hash_buckets = 10 .......... # SMALL # sequence_cache_hash_buckets = 23 ..........# MEDIUM # sequence_cache_hash_buckets = 89 ..........# LARGE # audit_trail = true ..........# if you want auditing # timed_statistics = true ..........# if you want timed statistics max_dump_file_size = 10240 ..........# limit trace file size to 5 Meg each # log_archive_start = true ..........# if you want automatic archiving # define directories to store trace and alert files background_dump_dest=%RDBMS80%\trace user_dump_dest=%RDBMS80%\trace db_block_size = 2048 snapshot_refresh_processes = 1 remote_login_passwordfile = shared compatible = 8.0.0.0.0 2. Click the NT Instance Manager v8.0 icon in the Oracle for Windows NT program group.
NOTE - If the NT Instance Manager does not appear on the Start menu, you can invoke it by double-clicking the executable file ORADIM80.EXE from Windows Explorer. The default location of this program is the \ORANT\BIN subdirectory.Click the New button and in the dialog box that appears, type in the name and location of the initialization file you created in step 1 in the Parameter Initialization Filename field. Type in the system identifier (a name of your choice up to four characters long) in the SID field. If you want the new instance to start as soon as it is created, check the Services and Instance check boxes in the Startup After Creation section of the New Instance dialog box. If you want the instance to start automatically whenever the host computer boots, check Automatic in the Instance Startup Mode section of the New Instance dialog box. These options are indicated in Figure 1.1.
3. Click the Advanced button and specify the same name in the Database Name field as you did for the DB_NAME parameter in the parameter initialization file. Specify the redo log file names and the names of the data files in the appropriate fields. You can specify more than one of each of these file types by separating the filenames with commas. It is easy to add more data files or redo log files later, so there is no need to agonize over these decisions now. Take particular care with the values of MAXDATAFILES, MAXLOGFILES, and MAXLOGMEMBERS, though. Changing these values later requires a control file rebuild, an intricate process. An example of the advanced parameters screen appears in Figure 1.2.
4. Click the OK button to create the Oracle instance.
5. Instance Manager presents a dialog box reporting that it has created a password file for the database and that password file use depends on the value of the initialization parameter REMOTE_LOGIN_PASWORDFILE. Click OK. How-To 1.3 covers password file administration in depth.
6. The Create Instance Information dialog box asks whether you want to run the catalog.sql and catproc.sql scripts. These scripts configure the data dictionary views and procedural options. You can choose to run the scripts now, or you can wait and run them outside this instance creation procedure by using the create.sql file in c:\orant\bin.
How It Works
Instance Manager writes a script to create the database that will contain, by default, two redo log files and one system file. The installer also writes a create.sql file that connects to the newly created instance and runs the catalog.sql and catproc.sql scripts to create the data dictionary views and enable the procedural option, respectively. Comments Instance Manager is also useful if you need to delete an instance. It will remove the NT service and the password file created for the instance; the DBA need only remove the remaining physical files to complete instance removal. If you did choose to run the catproc.sql and catalog.sql scripts during the installation procedure or if you ran the create.sql script without modifying it, the new instance will be shut down at the completion of these steps. See How To 1.2 to learn how to connect to the new instance from Enterprise Manager, SQL*Plus, or Server Manager. Instance Manager does not provide a forum to build a complicated database architecture. It is best used to configure a minimally functional database. The only details worth deliberating at any length, when using Instance Manager to build a new Oracle instance, are those that are difficult to change later, as specified in Step 3.