An example of Oracle10g multi-instance configuration-mysql tutorial

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; my company, as a technical integrator, put forward various suggestions and ultimately failed. So we proposed two solutions: the first one is an oracle database instance with multiple solutions.

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; my company, as a technical integrator, put forward various suggestions and ultimately failed. So we proposed two solutions: the first one is an oracle database instance with multiple solutions.

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

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 =
(SID = abc)
(SERVICE = DEDICATED)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.233.150) (PORT = 1521 ))
)
(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,201 0, 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, has 1 handler (s) for this service...
Service "abc" has 1 instance (s ).
Instance "abc", status UNKNOWN, has 1 handler (s) for this service...
Service "orcl" has 1 instance (s ).
Instance "orcl", status UNKNOWN, has 1 handler (s) for this service...
The command completed successfully

7. copy the data file of the orcl instance (1) the database must be copied in a consistent state, and the orcl database instance must be closed.

$ Sqlplus/as sysdba;
SQL> shutdown immediate;
SQL> quit

(2) copy all data files of the 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 14 sysaux01.dbf
-Rw-r ----- 1 oracle oinstall 461381632 Nov 14 system01.dbf
-Rw-r ----- 1 oracle oinstall 20979712 Nov 14 temp01.dbf
-Rw-r ----- 1 oracle oinstall 26222592 Nov 14 undotbs01.dbf
-Rw-r ----- 1 oracle oinstall 5251072 Nov 14 users01.dbf
$

8. starting to create abc instance (1) Start database through abc SID

[Oracle @ node1 ~] $ Export ORACLE_SID = abc
[Oracle @ node1 ~] $ Sqlplus/as sysdba;

SQL * Plus: Release 10.2.0.5.0-Production on 14 22:18:52 2014

Copyright (c) 1982,201 0, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL>

(2) start the abc instance to the nomount status

Because the pfile parameter file of the abc instance exists, the database can be started to nomount.

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 control file in nomount state, and manually copy and paste the statements created above to generate the control file.

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 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
18;

Control file created.

SQL>
SQL>

(4) resetlogs starts the database

Resetlogs starts the database and generates a new redo log file.

SQL> alter database open resetlogs;

Database altered.

(5) create a spfile file through 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 DBID (1) of the Oracle database before nid, you must shutdown immediate and then mount it again;

SQL> shutdown immediate;
SQL> startup mount;
SQL> quit

(2) nid: modify the database name

[Oracle @ node1 ~] $ Nid target = sys

DBNEWID: Release 10.2.0.5.0-Production on 14 22:46:50 2014

Copyright (c) 1982,200 7, 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 modification is successful.

$ 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 auto-start upon instance startup (1) configure oracle boot upon startup and add the abc instance auto-start switch item

# 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 startup

# Vi/etc/rc. local
#! /Bin/sh
Su-oracle-c "lsnrctl start"
Su-oracle-c "dbstart"

(3) how to manually start and close a database instance after logging on to the database server

How to enter the orcl instance;
$ Export ORACLE_SID = orcl
$ Sqlplus/nolog
SQL> conn/as sysdba
SQL> startup;

Method for accessing the abc instance;
$ Export ORACLE_SID = abc
$ Sqlplus/nolog
SQL> conn/as sysdba
SQL> startup