An example of Oracle10g multi-instance Configuration

Background: At a Deployment Solution seminar organized by Party A, various developers put forward their own server requirements. Party A only temporarily transitioned the use of one database and required the integration of several oracle databases; our company as a technical whole

Background: At a Deployment Solution seminar organized by Party A, various developers put forward their own server requirements. Party A only temporarily transitioned the use of one database and required the integration of several oracle databases; our company as a technical whole

Background: At a Deployment Solution seminar organized by Party A, various developers put forward their own server requirements. Party A only temporarily transitioned the use of one database and required the integration of several oracle databases; as a technical integrator, although my company put forward various suggestions, it was fruitless, So I proposed two solutions:
Solution 1: Multiple Solutions (users) are deployed in an oracle database instance, and an application is assigned a database account (user ).
Second, deploy multiple instances, and use one application as a separate instance.

The two solutions have their own advantages and disadvantages. Because developers say they are sensitive to their data, they propose a multi-instance deployment solution. The multi-instance method is to create multiple database instances on an oracle database server, running at the same time, the application database layer does not interfere with each other. There are many ways to create multiple instances. Here we take two instances as an example:

(1) After a database instance is created, you can directly use DBCA to create another instance, which is suitable for new installation and convenient and fast.
(2) clone a database instance from an existing instance database.
(3) restore a database instance on the local machine through rman backup.

Notes for installing multiple instances: memory usage is high. Both database instances need to allocate SGA, PGA, and other memory separately. Pay attention to the control of memory allocation between the two instances, to avoid the impact of excessive memory allocation on the host system.
The second method of this article is to deploy multiple instances and only perform installation tests. This example ignores the content such as sga memory allocation. This article reviews the physical structure of the database, this method is also applicable to the 11g for linux version.

1. database environment (1) Current Database situation

Operating system version: OEL5.8 x64
Database Version: Oracle 10.2.0.5 x64
Database Name: orcl
Database SID: orcl
Instance name: orcl
Database File Path:/u01/app/oracle/oradata/orcl/

(2) database to be cloned

Database Name: abc
Database SID: abc
Instance name: abc
Database File Path:/u01/app/oracle/oradata/abc/
Note: The two database instances use different directory structures and database names.

2. Prepare the directory structure of the abc instance.

# Su-oracle
$ Mkdir-p/u01/app/oracle/admin/abc/{adump, bdump, cdump, dpdump, udump, pfile}
$ Mkdir-p/u01/app/oracle/oradata/abc/

3. Prepare the parameter file of the abc instance.

Modify the parameter file of an existing orcl instance.

$ Sqlplus/nolog
SQL> conn/as sysdba;
SQL> create pfile from spfile;
SQL> host cp $ ORACLE_HOME/dbs/initorcl. ora $ ORACLE_HOME/dbs/initabc. ora
SQL> host vi $ ORACLE_HOME/dbs/initabc. ora

# Change orcl to an abc instance and check whether the path is correct. Abc. _ db_cache_size = 293601280 abc. _ java_pool_size = 4194304 abc. _ large_pool_size = 4194304 abc. _ shared_pool_size = 117440512 abc. _ streams_pool_size = 0 *. audit_file_dest = '/u01/app/oracle/admin/abc/adump '*. background_dump_dest = '/u01/app/oracle/admin/abc/bdump '*. compatible = '10. 2.0.5.0 '*. control_files = '/u01/app/oracle/oradata/abc/control01.ctl', '/u01/app/oracle/oradata/abc/control02.ctl ', '/u01/app/oracle/oradata/abc/control03.ctl '*. core_dump_dest = '/u01/app/oracle/admin/abc/cdump '*. db_block_size = 8192 *. db_domain = ''*. db_file_multiblock_read_count = 16 *. db_name = 'abc '*. db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area '*. db_recovery_file_dest_size = 2147483648 *. dispatchers = '(PROTOCOL = TCP) (SERVICE = abcXDB )'*. job_queue_processes = 10 *. open_cursors = 300 *. pga_aggregate_target = 141557760 *. processes = 150 *. remote_login_passwordfile = 'clusive '*. sga_target = 425721856 *. undo_management = 'auto '*. undo_tablespace = 'undotbs1 '*. user_dump_dest = '/u01/app/oracle/admin/abc/udump'

4. Create an SQL statement for the Control File

Because the control file contains database file location, Instance name, and other data, you need to create a new control file based on the new instance name, data file, and file path.
Create a control file based on the control file trace file.

SQL> alter database backup controlfile to trace;
# View the file name of the just-created comprehensive file:
SQL> oradebug setmypid
SQL> oradebug tracefile_name
/U01/app/oracle/admin/orcl/udump/orcl_ora_4044.trc

SQL>! Cat/u01/app/oracle/admin/orcl/udump/orcl_ora_4044.trc

# Use the following format to add other data files.
Create controlfile set DATABASE "ABC" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1'/u01/app/oracle/oradata/abc/redo01.log' SIZE 50 M,
GROUP 2'/u01/app/oracle/oradata/abc/redo02.log 'SIZE 50 M,
GROUP 3 '/u01/app/oracle/oradata/abc/redo03.log' SIZE 50 M
-- STANDBY LOGFILE
DATAFILE
'/U01/app/oracle/oradata/abc/system01.dbf ',
'/U01/app/oracle/oradata/abc/undotbs01.dbf ',
'/U01/app/oracle/oradata/abc/sysaux01.dbf ',
'/U01/app/oracle/oradata/abc/users01.dbf'
Character set ZHS16GBK
;

5. Create a password file

$ Orapwd file = $ ORACLE_HOME/dbs/orapwabc password = oracle entries = 10

6. Configure multiple instance listeners and service names (1) Add static listener configurations to achieve single IP address and multiple instances

$ Vi $ ORACLE_HOME/network/admin/listener. ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
(SID_DESC =
(SID_NAME = abc)
(ORACLE_HOME =/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = abc)
)
)