主机192.168.50.191
备机192.168.50.192
安装前配置
1. 解压安装文件
unzip linux_11gR2_database_1of2.zip
unzip linux_11gR2_database_2of2.zip
chmod -R 777 /opt
2.修改 /etc/hosts
本机IP地址 主机名 localhost
192.168.1.210 linuxserver localhost
3. 设置内核参数/etc/sysctl.conf
# This is Oracle 10g sysctl Kernel Setting by heweilong 20110415
# 可以使用的共享内存的总量 (最小内存)
kernel.shmall = 2097152
# 最大共享内存段大小 (内存大小)
kernel.shmmax = 1073741824
# 整个系统共享内存段的最大数目 (4096)
kernel.shmmni = 4096
# 每个信号对象集的最大信号对象数;系统范围内最大信号对象数;每个信号对象支持的最大操作数;系统范围内最大信号对象集数 (250 32000 100 128)
kernel.sem = 5010 641280 5010 128
# 系统中所允许的文件句柄最大数目 (65536)
fs.file-max = 65536
# 应用程序可使用的IPv4端口范围 (1024 65000)
net.ipv4.ip_local_port_range = 1024 65000
# 套接字接收缓冲区大小的缺省值 (262144)
net.core.rmem_default = 1048576
# 套接字接收缓冲区大小的最大值 (262144)
net.core.rmem_max = 1048576
# 套接字发送缓冲区大小的缺省值 (262144)
net.core.wmem_default = 262144
# 套接字发送缓冲区大小的最大值 (262144)
net.core.wmem_max = 262144
# 禁止ping主机 (0)
# net.ipv4.icmp_echo_ignore_all = 1
oracle11g 也可不设置,系统会自动更改
fs.file-max = 65536
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_max = 1048576
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
4.重新编译内核执行生效
# /sbin/sysctl -p
5. 编辑/etc/security/limits.conf 添加
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
或者
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
6.编辑/etc/pam.d/login 添加 (可选)
session required pam_limits.so
7. 修改/etc/selinux/config文件(可选)
SELINUX=disabled
8. 创建组和用户
# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba oracle
# passwd oracle
设置密码
9. 创建安装文件夹(可选)
# mkdir -p /opt/oracle/ora11g
# chown -R oracle.oinstall /opt
10. 修改XManager显示(可选)
xhost +IP
# xhost +192.168.1.210
11. 修改版/etc/redhat-release(可选)
# Red Hat Enterprise Linux Server release 5.3 (Tikanga)
Red Hat Enterprise Linux Server release 4 (Tikanga)
12. 编辑/opt/oracle.bash_profile
# This is Oracle 10g user .bash_profile Setting by heweilong 20110415
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export ORACLE_SID=primary
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
13. 注销root用户使用oracle用户登录系统安装数据库
执行/opt/database
#./runInstaller
安装数据库软件
1.仅安装数据库软件
2.单实例数据库
3.企业版
4.打依赖包(如果在安装包时,提示有关联,可以强制执行加入参数 –nodeps)
# rpm -ivh libaio-devel-0.3.106-5.i386.rpm
# rpm -ivh sysstat-7.0.2-11.el5.i386.rpm
# rpm -ivh –nodeps unixODBC-2.2.11-10.el5.i386.rpm
# rpm -ivh –nodeps unixODBC-devel-2.2.11-10.el5.i386.rpm
[root@node2 Server]# rpm -ivh libaio-devel-0.3.106-5.i386.rpm
warning: libaio-devel-0.3.106-5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing… ########################################### [100%]
1:libaio-devel ########################################### [100%]
[root@node2 Server]# rpm -ivh sysstat-7.0.2-11.el5.i386.rpm
warning: sysstat-7.0.2-11.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing… ########################################### [100%]
1:sysstat ########################################### [100%]
[root@node2 Server]# rpm -ivh –nodeps unixODBC-2.2.11-10.el5.i386.rpm
warning: unixODBC-2.2.11-10.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing… ########################################### [100%]
1:unixODBC ########################################### [100%]
[root@node2 Server]# rpm -ivh –nodeps unixODBC-devel-2.2.11-10.el5.i386.rpm
warning: unixODBC-devel-2.2.11-10.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
Preparing… ########################################### [100%]
1:unixODBC-devel ########################################### [100%]
5.安装数据库
安装数据库实例(主机中安装备机不需要安装)
1.实例名primary
2.数据库开启归档
3.字符集设置为ZHS16GBK
4.保存脚本
5.使用“监听程序配置”来创建从库的监听器
配置Data Guard配置文件
1.主机配置(192.168.50.191)开启数据库归档模式
如果出现乱码执行
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
$ SQLplus /nolog
SQL> conn / as sysdba
SQL> startup;
SQL> alter database force logging;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> archive log list
[root@node1 ~]# su – oracle
[oracle@node1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 6 10:20:58 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> startup;
ORACLE instance started.
Total System Global Area 1046224896 bytes
Fixed Size 1341140 bytes
Variable Size 603982124 bytes
Database Buffers 436207616 bytes
Redo Buffers 4694016 bytes
Database mounted.
Database opened.
SQL> alter database force logging;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 949972992 bytes
Fixed Size 1340496 bytes
Variable Size 553651120 bytes
Database Buffers 390070272 bytes
Redo Buffers 4911104 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/archive
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
主机创建密码文件
orapwd file=’/opt/oracle/oradata/orapwstandby.ora’ password=sys entries=5
$ orapwd file=’/opt/oracle/oradata/orapwstandby.ora’ password=sys entries=5
主机创建控制文件命名为standby.ctl :
SQL> alter database create standby controlfile as ‘/opt/oracle/oradata/standby.ctl’;
SQL> alter database create standby controlfile as ‘/opt/oracle/oradata/standby.ctl’;
Database altered.
主机创建pfile参数文件命名为standby.ora
SQL> create pfile=’/opt/oracle/oradata/initSTANDBY.ora’ from spfile;
SQL> create pfile=’/opt/oracle/oradata/initstandby.ora’ from spfile;
File created.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
initSTANDBY.ora文件复制至本地
创建两份第一份命名为initPRIMARY.ora第二份为initSTANDBY.ora
备机pfile文件
将pfile文件initSTANDBY.ora复制至备机/opt/oracle/ora11g/dbs/initSTANDBY.ora目录下
备机密码文件
将密码文件orapwPRIMARY.ora更名为orapwSTANDBY.ora存放至备机/opt/oracle/ora11g/dbs/orapwSTANDBY.ora目录下
控制文件
将控制文件standby.ora 复制到备机 /opt/oracle/oradata/standby.ora 目录下
备机配置(192.168.50.192)创建文件夹
mkdir -p /opt/oracle/oradata/STANDBY;
mkdir -p /opt/oracle/admin/STANDBY/adump;
mkdir -p /opt/oracle/admin/STANDBY/bdump;
mkdir -p /opt/oracle/admin/STANDBY/cdump;
mkdir -p /opt/oracle/admin/STANDBY/dpdump;
mkdir -p /opt/oracle/admin/STANDBY/pfile;
mkdir -p /opt/oracle/admin/STANDBY/udump;
主机中数据文件和控制文件复制至备机,使用scp命令复制
[oracle@node1 ~]$ cd /opt/oracle/
[root@node1 oracle]# scp -r oradata/ 192.168.50.192:/opt/oracle/
root@192.168.50.192’s password:
standby.ctl 100% 9520KB 9.3MB/s 00:00
initSTANDBY.ora 100% 933 0.9KB/s 00:00
orapwSTANDBY.ora 100% 2048 2.0KB/s 00:00
redo03.log 100% 50MB 25.0MB/s 00:02
sysaux01.dbf 100% 450MB 21.4MB/s 00:21
undotbs01.dbf 100% 75MB 25.0MB/s 00:03
system01.dbf 100% 680MB 21.3MB/s 00:32
users01.dbf 100% 5128KB 5.0MB/s 00:00
control01.ctl 100% 9520KB 9.3MB/s 00:00
temp01.dbf 100% 29MB 29.0MB/s 00:00
control02.ctl 100% 9520KB 4.7MB/s 00:02
redo01.log 100% 50MB 25.0MB/s 00:02
redo02.log 100% 50MB 16.7MB/s 00:03
使用vi编辑initstandby.ora文件
主库修改
db_unique_name=PRIMARY
log_archive_config=’DG_CONFIG=(PRIMARY,STANDBY)’
log_archive_dest_1=’LOCATION=/opt/oracle/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY’
log_archive_dest_2=’SERVICE=STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY’
fal_server=STANDBY
fal_client=PRIMARY
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
log_archive_max_processes=10
standby_file_management=AUTO
db_file_name_convert=’oradata/STANDBY’,’oradata/PRIMARY’
log_file_name_convert=’oradata/STANDBY’,’oradata/PRIMARY’
备库修改
db_unique_name=STANDBY
log_archive_config=’DG_CONFIG=(PRIMARY,STANDBY)’
log_archive_dest_1=’LOCATION=/opt/oracle/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY’
log_archive_dest_2=’SERVICE=PRIMARY ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY’
fal_server=STANDBY
fal_client=PRIMARY
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
log_archive_max_processes=10
standby_file_management=AUTO
db_file_name_convert=’oradata/PRIMARY’,’oradata/STANDBY’
log_file_name_convert=’oradata/PRIMARY’,’oradata/STANDBY’
主库实例
PRIMARY.__db_cache_size=436207616
PRIMARY.__java_pool_size=4194304
PRIMARY.__large_pool_size=4194304
PRIMARY.__oracle_base=’/opt/oracle’#ORACLE_BASE set from environment
PRIMARY.__pga_aggregate_target=419430400
PRIMARY.__sga_target=629145600
PRIMARY.__shared_io_pool_size=0
PRIMARY.__shared_pool_size=176160768
PRIMARY.__streams_pool_size=0
*.audit_file_dest=’/opt/oracle/admin/PRIMARY/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/opt/oracle/oradata/PRIMARY/control01.ctl’,’/opt/oracle/oradata/PRIMARY/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’PRIMARY’
*.diagnostic_dest=’/opt/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMARYXDB)’
*.memory_target=1048576000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
db_unique_name=PRIMARY
log_archive_config=’DG_CONFIG=(PRIMARY,STANDBY)’
log_archive_dest_1=’LOCATION=/opt/oracle/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY’
log_archive_dest_2=’SERVICE=STANDBY ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY’
fal_server=STANDBY
fal_client=PRIMARY
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
log_archive_max_processes=10
standby_file_management=AUTO
db_file_name_convert=’oradata/STANDBY’,’oradata/PRIMARY’
log_file_name_convert=’oradata/STANDBY’,’oradata/PRIMARY’
备库实例
STANDBY.__db_cache_size=436207616
STANDBY.__java_pool_size=4194304
STANDBY.__large_pool_size=4194304
STANDBY.__oracle_base=’/opt/oracle’#ORACLE_BASE set from environment
STANDBY.__pga_aggregate_target=419430400
STANDBY.__sga_target=629145600
STANDBY.__shared_io_pool_size=0
STANDBY.__shared_pool_size=176160768
STANDBY.__streams_pool_size=0
*.audit_file_dest=’/opt/oracle/admin/STANDBY/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/opt/oracle/oradata/standby.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’PRIMARY’
*.diagnostic_dest=’/opt/oracle’
*.memory_target=1048576000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
db_unique_name=STANDBY
log_archive_config=’DG_CONFIG=(STANDBY,PRIMARY)’
log_archive_dest_1=’LOCATION=/opt/oracle/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY’
log_archive_dest_2=’SERVICE=PRIMARY ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY’
fal_server=PRIMARY
fal_client=STANDBY
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
log_archive_max_processes=10
standby_file_management=AUTO
db_file_name_convert=’oradata/PRIMARY’,’oradata/STANDBY’
log_file_name_convert=’oradata/PRIMARY’,’oradata/STANDBY’
修改监听配置文件并测试
修改主机上(192.168.50.191) 修改监听和TNS配置信息
/opt/oracle/ora11g/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/ora11g/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.191)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY)
(ORACLE_HOME = /opt/oracle/ora11g)
(SID_NAME = PRIMARY)
)
)
/opt/oracle/ora11g/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/ora11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.191)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = PRIMARY)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.192)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
)
)
修改备机上(192.168.50.192) 修改监听和TNS配置信息
/opt/oracle/ora11g/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/oracle/ora11g/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.192)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY)
(ORACLE_HOME = /opt/oracle/ora11g)
(SID_NAME = STANDBY)
)
)
/opt/oracle/ora11g/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/ora11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.191)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMARY)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.192)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STANDBY)
)
)
主备机tnsping连通性测试
在主库(192.168.50.191)上tnsping测试
tnsping standby
tnsping primary
在备库(192.168.50.192)上tnsping测试
tnsping primary
tnsping standby
关闭备库(192.168.50.192)并开启standby模式
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 949972992 bytes
Fixed Size 1340496 bytes
Variable Size 553651120 bytes
Database Buffers 390070272 bytes
Redo Buffers 4911104 bytes
SQL> alter database mount standby database;
Database altered.
备份库开启日志应用服务
SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database disconnect from session;
Database altered.
创建表和数据错测试
在主库上以sysdba执行:
SQL> conn system/system@PRIMARY
SQL> create table test1(row1 varchar2(50));
SQL> insert into test1 values(‘heweilo@hotmail.com’);
SQL> commit;
SQL> alter system archive log current;
SQL> create table test1(row1 varchar2(50));
Table created.
SQL> insert into test1 values(‘heweilo@hotmail.com’);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
主库提交归档信息
SQL> alter system switch logfile;
SQL> alter system switch logfile;
System altered.
备库归档日志并提供查询操作
SQL> select sequence#,applied from v$archived_log order by sequence#;
SQL> select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
———- ———
27 YES
28 YES
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
SEQUENCE# APPLIED
———- ———
38 YES
12 rows selected.
备份库归档日志并且打开数据库
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
即可进行备库信息查询
主库备库切换操作
主库上查询切换状态(要有TO STANDBY)
SQL> select switchover_status from v$database;
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
TO STANDBY
查询关闭多余会话
SQL> select program,type from v$session where type=’USER’;
SQL> select program,type from v$session where type=’USER’;
PROGRAM TYPE
———————————————— ———-
sqlplus@node1 (TNS V1-V3) USER
主库上执行切换,切换到备库
SQL > alter database commit to switchover to physical standby with session shutdown;
SQL> alter database recover managed standby database disconnect from session;
Database altered.
查询备库alert_STANDBY.log最后一行(可能有延迟)表示切换成功
MRP0: Media Recovery Complete: End-Of-REDO (STANDBY)
MRP0: Background Media Recovery process shutdown (STANDBY)
备库上执行切换,切换成主库了
SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
打开新的主库(原来的备库)
SQL> alter database open;
SQL> alter database open;
Database altered.
重启备库(原来的主库)
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1046224896 bytes
Fixed Size 1341140 bytes
Variable Size 603982124 bytes
Database Buffers 436207616 bytes
Redo Buffers 4694016 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
主备切换完成
注意点启动多看看alert.log
监听,TNS,.bash_profile
主库备库HOSTS文件相同(127.0.0.1必须要有)
# Do not remove the following line, or various programs
# that require network functionality will fail.
192.168.50.191 node1
192.168.50.192 node2
127.0.0.1 localhost.localdomain localhost
主机
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export ORACLE_SID=PRIMARY
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
备机
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/ora11g
export ORACLE_SID=STANDBY
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib