An example of Oracle 10g multi-instance configuration

Background: A one-party organization of the deployment of the seminar, the developers have put forward their own server requirements, party a only to a database temporary transition to use, requires the integration of several applications Oracle database; My company as a technology integration side, although the proposal, ultimately no results, then put forward two options:
The first is a multi-scenario (user)-mode deployment of an Oracle database instance, and one application assigns a database account number (user).
The second is that one is deployed in a multi-instance manner and a single instance is applied.

Each of the two options have pros and cons, because each developer said their data is sensitive, so the choice of multi-instance deployment of the scheme, multiple instances of the way is to create multiple DB instances in an Oracle database server, running at the same time, the application database level does not interfere. There are many ways to create multiple instances, and here are examples of two examples:

(1) Once a database instance is created, another instance is created directly using DBCA, which is convenient and fast for a new installation.
(2) Cloning a DB instance from an existing instance database.
(3) Recover a DB instance from the local computer by using Rman backup.

Install multi-instance needs to pay attention to the problem, memory consumption, two DB instances should be allocated SGA,PGA and other memory, two instance memory allocation attention control, so as to avoid excessive allocation of memory impact on the host system.
The second way to deploy multi-instance, only to do the installation test, the SGA memory allocation and other content of this example ignored, through this article on the database of the physical structure of a review, this way for the 11g for Linux version as applicable.

1. Database environment (1) now a 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/
Description: Two DB instances use different directory structures and database names.

2. Prepare the directory structure for 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 for the ABC instance

Modifications are made through the parameters file of an existing ORCL instance.

$ sqlplus/nolog  
sql> conn/as sysdba;    
sql> create Pfile from SPFile;    < Br>sql> host CP $ORACLE _home/dbs/initorcl.ora $ORACLE _home/dbs/initabc.ora    
Sql> Host VI $ORACLE _ Home/dbs/initabc.ora

#将orcl改为abc实例, note that 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= ' EXCLUSIVE ' &nbsP;*.sga_target=425721856 *.undo_management= ' AUTO '  *.undo_tablespace= ' UNDOTBS1 '  *.user_dump_dest = '/u01/app/oracle/admin/abc/udump '

4. Creating a control File SQL statement

Because the control file includes the database file location, instance name and other data, you need to re-create the control file according to the new instance name and the data file and file path.
Creates a control file based on the controls file trace file.

sql> ALTER DATABASE backup Controlfile to trace;
# View the file name of the profile you just created:
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

# in the following format, if there are other data files, you can join in this format.
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 50M,
GROUP 2 '/u01/app/oracle/oradata/abc/redo02.log ' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/abc/redo03.log ' SIZE 50M
--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. Multi-instance listener and service name configuration (1) Add static listening configuration, implement single IP, multi-instance

$ 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)
)
)

LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.233.150) (PORT = 1521))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))
)
)

(2) Command service configuration

$ vi $ORACLE _home/network/admin/tnsnames.ora  
ABC =    
  (DESCRIPTION =    
    (address_list =    
      (ADDRESS = (PROTOCOL = TCP) (HOST = 192 .168.233.150) (PORT = 1521))    ,
   )    
    (Connect_data = & nbsp  
      (SID = ABC)   &NBSP;
      (SERVICE = Dedicated)    
   )    
 )

ORCL =  
  (DESCRIPTION =    
    (address_list =    
 &nb sp;    (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.233.150) (PORT = 1521))    
  &nbs P )    
    (connect_data =    
      (SID = ORCL)    
      (SERVICE = dedicated)    
   )    
 )

Extproc_connection_data =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))
)
(Connect_data =
(SID = Plsextproc)
(PRESENTATION = RO)
)
)

$ lsnrctl Stop
$ lsnrctl Start

Lsnrctl for linux:version 10.2.0.5.0-production on 14-11014 22:07:31

Copyright (c) 1991, Oracle. All rights reserved.

Starting/u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr:please wait ...

Tnslsnr for Linux:version 10.2.0.5.0-production
System parameter File Is/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages Written To/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (Description= (Address= (protocol=tcp) (Host=node1) (port=1521)))
Listening on: (Description= (Address= (PROTOCOL=IPC) (KEY=EXTPROC0)))

Connecting to (Description= (address= (protocol=tcp) (Host=node1) (port=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for Linux:version 10.2.0.5.0-production
Start Date 14-11014 22:07:31
Uptime 0 days 0 hr. 0 min. 0 sec
Trace level off
Security on:local OS Authentication
SNMP OFF
Listener Parameter File/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary ...
(Description= (address= (protocol=tcp) (Host=node1) (port=1521)))
(Description= (address= (PROTOCOL=IPC) (KEY=EXTPROC0)))
Services Summary ...
Service "Plsextproc" has 1 instance (s).
Instance "Plsextproc", status UNKNOWN, have 1 handler (s) for the This service ...
Service "ABC" has 1 instance (s).
Instance "abc", Status UNKNOWN, have 1 handler (s) for the This service ...
Service "ORCL" has 1 instance (s).
Instance "ORCL", status UNKNOWN, have 1 handler (s) for the This service ...
The command completed successfully

7. Copy the data file for the ORCL instance (1) The database must be copied in a consistent state, and the ORCL DB instance needs to be closed.

$ sqlplus/as sysdba;
sql> shutdown immediate;
Sql> quit

(2) Copy all data files of ORCL instance

$ cp/u01/app/oracle/oradata/orcl/*.dbf/u01/app/oracle/oradata/abc/
$ ll/u01/app/oracle/oradata/abc/
Total 738672
-RW-R-----1 Oracle oinstall 262152192 Nov 22:12 sysaux01.dbf
-RW-R-----1 Oracle oinstall 461381632 Nov 22:12 system01.dbf
-RW-R-----1 Oracle oinstall 20979712 Nov 22:12 temp01.dbf
-RW-R-----1 Oracle oinstall 26222592 Nov 22:12 undotbs01.dbf
-RW-R-----1 Oracle oinstall 5251072 Nov 22:12 users01.dbf
$

8. Start creating the ABC instance (1) Start the database with the ABC SID

[Email protected] ~]$ export ORACLE_SID=ABC
[Email protected] ~]$ Sqlplus/as sysdba;

Sql*plus:release 10.2.0.5.0-production on 14 22:18:52 2014

Copyright (c) 1982, Oracle. All rights Reserved.

Connected to an idle instance.

Sql>

(2) Start ABC instance to nomount state

Because there is an pfile parameter file for the ABC instance, you can boot the database to the Nomount state.

sql> startup Nomount;
ORACLE instance started.

Total System Global area 427819008 bytes
Fixed Size 2096792 bytes
Variable Size 125829480 bytes
Database buffers 293601280 bytes
Redo buffers 6291456 bytes
Sql>

(3) Create a Build control file in Nomount state, manually copy and paste the generated control file statement created above.

Sql> 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 50M,
GROUP 2 '/u01/app/oracle/oradata/abc/redo02.log ' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/abc/redo03.log ' SIZE 50M
--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
18;

Control file created.

Sql>
Sql>

(4) resetlogs startup database

Resetlogs start the database and regenerate the redo log file.

sql> ALTER DATABASE open resetlogs;

Database altered.

(5) Create a SPFile file from the Pfile file of the ABC instance

Sql> create SPFile from Pfile;

(6) Add a temporary data file

sql> Alter tablespace temp add tempfile '/u01/app/oracle/oradata/abc/temp01.dbf '

9. Change the Oracle database dbid (1) before Nid must shutdown immediate once again mount State;

sql> shutdown immediate;
sql> startup Mount;
Sql> quit

(2) Nid modifies the database name

[email protected] ~]$ nid target=sys

Dbnewid:release 10.2.0.5.0-production on 14 22:46:50 2014

Copyright (c) 1982, Oracle. All rights reserved.

Password:
Connected to Database ABC (dbid=1390560469)

Connected to server version 10.2.0

Control Files in database:
/u01/app/oracle/oradata/abc/control01.ctl
/u01/app/oracle/oradata/abc/control02.ctl
/u01/app/oracle/oradata/abc/control03.ctl

Change database ID of database ABC? (Y/[n]) = Y

Proceeding with Operation
Changing database ID from 1390560469 to 1819805470
Control file/u01/app/oracle/oradata/abc/control01.ctl-modified
Control file/u01/app/oracle/oradata/abc/control02.ctl-modified
Control file/u01/app/oracle/oradata/abc/control03.ctl-modified
Datafile/u01/app/oracle/oradata/abc/system01.dbf-dbid changed
Datafile/u01/app/oracle/oradata/abc/undotbs01.dbf-dbid changed
Datafile/u01/app/oracle/oradata/abc/sysaux01.dbf-dbid changed
Datafile/u01/app/oracle/oradata/abc/users01.dbf-dbid changed
Datafile/u01/app/oracle/oradata/abc/temp01.dbf-dbid changed
Control File/u01/app/oracle/oradata/abc/control01.ctl-dbid changed
Control File/u01/app/oracle/oradata/abc/control02.ctl-dbid changed
Control File/u01/app/oracle/oradata/abc/control03.ctl-dbid changed
Instance shut down

(3) Verify that the database name is modified and the final modification succeeds.

$ sqlplus/nolog
Sql> Conn/as sysdba;
sql> startup Mount;
sql> ALTER DATABASE open resetlogs;
Sql> select Dbid,name from V$database;

DBID NAME
---------- ---------
1819805470 ABC

Sql>

10. Configure instance boot from boot (1) Configure Oracle boot, add ABC instance self-starter switch

# Vi/etc/oratab
Orcl:/u01/app/oracle/product/10.2.0/db_1:y
Abc:/u01/app/oracle/product/10.2.0/db_1:y

(2) Set boot start

# vi/etc/rc.local
#!/bin/sh
Su-oracle-c "Lsnrctl Start"
Su-oracle-c "Dbstart"

(3) Logging on to the database server manually starting and shutting down the DB instance method

The method of entering ORCL instance;
$ Export ORACLE_SID=ORCL
$ sqlplus/nolog
Sql> Conn/as SYSDBA
Sql> startup;

Method of entering ABC instance;
$ Export ORACLE_SID=ABC
$ sqlplus/nolog
Sql> Conn/as SYSDBA
Sql> Startup

This article is from the "Koumm Linux Technology blog" blog, be sure to keep this source http://koumm.blog.51cto.com/703525/1576685

Oracle 10g Multi-instance configuration example