一. 数据库软件安装
参照官方手册
1.安装rpm包
注这里的yum直接用163的yum
yum -y install binutils compat-libstdc++-33 compat-libstdc++-33.i686 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc.i686 glibc-common glibc-devel glibc-devel.i686 glibc-headers ksh libaio libaio.i686 libaio-devel libaio-devel.i686 libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel make sysstat libXp libXt.i686 libXtst.i686
2..文件存储类型
注测试我选择文件系统,根据生产环境需求
3.检查硬件是否满足要求
至少1G RAM swap按以下要求设置,/tmp文件400M,磁盘空间
查询内存，swap,临时文件
4. 添加host文件添加对应记录
5. 添加用户及组
/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/groupadd oper
[root@netdata-ora10g-118 ~]# /usr/sbin/useradd -g oinstall -G dba,oper oracle
[root@netdata-ora10g-118 ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: it is based on a dictionary word
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.
[root@netdata-ora10g-118 ~]#
6. 修改内核参数文件
vim /etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
修改完成之后重启
7. 修改shell限制
vim /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
Add or edit the following line in the /etc/pam.d/login file, if it does not
already exist:
session required pam_limits.so
3. Depending on the oracle user‘s default shell, make the following changes to the
default shell start-up file:
■ For the Bourne, Bash, or Korn shell, add the following lines to the
/etc/profile file (or the /etc/profile.local file on SUSE systems):
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
8.建oracle base 目录
这里创建oracle_base oracle_home
通常我们用以下目录
oracle_base
/u01/app/oracle
/u01/app/orauser
/opt/oracle/app/oracle
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=netdata
oracle_home
oracle_base /product/10.2.0/db_1
oracle_base /oraInventory
9.添加环境变量
在用户配置文件下添加以下内容
#Oracle Settings
export TMP=/tmp;
export TMPDIR=$TMP;
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export ORACLE_SID=netdata;
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;
chown -R oracle:oinstall /u01
10. 拷贝安装文件并解压
gunzip 10201_database_linux_x86_64.cpio
cpio -idmv <10201_database_linux_x86_64.cpio
11.安装VNCServer
yum -y install vnc-serve
在vim /etc/sysconfig/vncservers
VNCSERVERS="1:root"
VNCSERVERARGS[1]="-name root -geometry 1280x720"
vim .vnc/xstartup 注释并添加
#twm &
gnome-session &
service vncserver restart
12. 编辑/etc/redhat-release文件
# vi /etc/redhat-release
将其中的内容CentOS release 6.5 (Final)修改为redhat 4
发现缺包[libawt.so: libXt.so.6]导致，找了半天并确定缺少下边的安装包
yum install libXt.i686 libXtst.i686
这里选择仅安装软件
二.手工建库
1.生成参数文件 ,并修改参数文件
注:注意 大小写
cd /u01/oracle/product/11.2.0/db_1/dbs
[oracle@oracleasm dbs]$ cat init.ora | grep -v ^# | grep -v ^$ >initNETDATA.ora
cat initNETDATA.ora
db_name=‘NETDATA‘
sga_max_size=700M
sga_target=652M
db_files = 80 # SMALL
db_file_multiblock_read_count = 8 # SMALL
processes = 1000 # SMALL
parallel_max_servers = 5 # SMALL
#log_buffer = 32768 # SMALL
#max_dump_file_size = 10240 # limit trace file size to 5 Meg each
global_names = TRUE
control_files = (/u01/app/oracle/oradata/NETDATA/ora_control1, /u01/app/oracle/oradata/NETDATA/ora_control2)
dispatchers=‘(PROTOCOL=TCP) (SERVICE=PRODXDB)‘
open_cursors=300
remote_login_passwordfile=‘EXCLUSIVE‘
undo_tablespace=‘UNDOTBS1‘
undo_management = AUTO
db_recovery_file_dest=‘/u01/app/oracle/flash_recovery_area‘
audit_file_dest=‘$ORACLE_HOME/admin/NETDATA/adump‘
background_dump_dest=‘$ORACLE_HOME/admin/NETDATA/bdump‘
core_dump_dest=‘$ORACLE_HOME/admin/NETDATA/cdump‘
user_dump_dest=‘$ORACLE_HOME/admin/NETDATA/udump‘
pga_aggregate_target = 195M
processes = 1000
sessions = 1200
open_cursors = 1024
db_recovery_file_dest_size = 4G
compatible = 10.2.0
2.运行脚本
启动到nomount
CREATE DATABASE NETDATA
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/NETDATA/redo10.log‘,‘/u01/app/oracle/oradata/NETDATA/redo11.log‘) SIZE 100M,
GROUP 2 (‘/u01/app/oracle/oradata/NETDATA/redo20.log‘,‘/u01/app/oracle/oradata/NETDATA/redo21.log‘) SIZE 100M,
GROUP 3 (‘/u01/app/oracle/oradata/NETDATA/redo30.log‘,‘/u01/app/oracle/oradata/NETDATA/redo31.log‘) SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘/u01/app/oracle/oradata/NETDATA/system01.dbf‘ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/NETDATA/sysaux01.dbf‘ SIZE 325M REUSE
DEFAULT TABLESPACE USERS
DATAFILE ‘/u01/app/oracle/oradata/NETDATA/USER01.dbf‘ SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE TEMPTS
TEMPFILE ‘/u01/app/oracle/oradata/NETDATA/temp01.dbf‘
SIZE 200M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE ‘/u01/app/oracle/oradata/NETDATA/undotbs01.dbf‘
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
3.创建完毕跑脚本
CONNECT SYS/password AS SYSDBA
@/u01/oracle/rdbms/admin/catalog.sql
@/u01/oracle/rdbms/admin/catproc.sql
EXIT
注释
catalog.sql 创建数据库字典视图动态性能视图公共同义词,创建同义词权限
catproc.sql pl/sql运行需要的脚本
|
Script |
Description |
|
CATALOG.SQL |
Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLICaccess to the synonyms. |
|
CATPROC.SQL |
Runs all scripts required for or used with PL/SQL. |
手工建库完毕
4. 监听配置
listener.ora
If you configured the dedicated server mode using the DBCA Connection Mode tab on the Initialization Parameters page, then DBCA automatically configures the LOCAL_LISTENER parameter when the listener uses a nondefault address port.
如果使用dbca创建的自动会创建好监听,使用默认端口1521监听
For example, to configure the LOCAL_LISTENER parameter, add the following entry to the initialization parameter file, where listener_sid is resolved to a listener address through either a tnsnames.ora file or through the Oracle Names Server:
客户端用tnames.ora 服务端listener.ora
客户端通过tnames.ora解析连接到服务端
Services coordinate their sessions using listener file entries by running a process on the server that receives connection requests on behalf of a client application. Listeners are configured to respond to connection requests sent to protocol addresses for a database service or non-database service.
Protocol addresses are configured in the listener configuration file, listener.ora, for a database service or a non-database service. Clients configured with the same addresses can connect to a service through the listener.
During a preconfigured database configuration installation, Oracle Net Configuration Assistant creates and starts a default listener called LISTENER_NODENAME. The listener is configured with default protocol listening addresses for the database and external procedures. The advanced installation process prompts you to create at least one listener with Oracle Net Configuration Assistant. The listener is configured to respond to connection requests that are directed to one protocol address you specify, as well as an address for external procedures.
Both installation modes configure service information about the RAC database and external procedures. An Oracle Database 10g Release 2 (10.2) database service automatically registers its information with the listener, such as its service name, instance names, and load information.
This feature, called service registration, does not require configuration in the listener.ora file. After listener creation, Oracle Net Configuration Assistant starts the listener. The following is an example listener.ora file with an entry for an instance named node1:
如果配置多个监听端口可配置如下
listener_node1=
(description=
(address=(protocol=ipc)(key=extproc))
(address=(protocol=tcp)(host=node1-vip)(port=1521)(IP=FIRST))
(address=(protocol=tcp)(host=node1-ip)(port=1521)(IP=FIRST)))
sid_list_listener_node1=
(sid_list=
(sid_desc=
(sid_name=plsextproc)
(oracle_home=/private/system/db)
(program=extproc)))
监听的原理
When a listener starts after the Oracle instance starts, and the listener is listed for service registration, registration does not occur until the next time the Oracle Database process monitor (PMON) discovery routine starts. By default, PMON discovery occurs every 60 seconds.
To override the 60-second delay, use the SQL ALTER SYSTEM REGISTER statement. This statement forces the PMON process to register the service immediately.
Oracle recommends that you create a script to run this statement immediately after starting the listener. If you run this statement while the listener is up and the instance is already registered, or while the listener is down, then the statement has no effect.
一个监听开始工作在一个实例启动后开始,监听注册
tnames.ora
A tnsnames.ora file is created on each node with net service names. A connect identifier is an identifier that maps to a connect descriptor. A connect descriptor contains the following information:
- The network route to the service, including the location of the listener through a protocol address
- The SERVICE_NAME for an Oracle release 8.1 or later, or sid for pre-8.1 Oracle releases
tnames.ora包含每个节点与之对应的服务名,一条连接标识映射连接描述
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
#(SID_NAME = PLSExtProc)
(SID_NAME = NETDATA)
(GLOBAL_DBNAME = NETDATA)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
#(PROGRAM = extproc)
)
#部存储过程配置的本地监听 PLSExtProc
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = NetData-ora10g-118)(PORT = 51518))
)
)
tnames.ora配置
NETDATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.213.118)(PORT = 51518))
)
(CONNECT_DATA =
(SERVICE_NAME = NETDATA)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
升级10.2.0.1 - 10.2.0.5
修改javapool,share_pool大小
三.升级数据库及软件
1. 备份软件及数据库,及关闭监听及数据库
[oracle@NetData-ora10g-118 app]$ cp -r oracle oracle.bak
[oracle@NetData-ora10g-118 app]$ pwd
/u01/app
[oracle@NetData-ora10g-118 app]$ ls
oracle oracle.bak
[oracle@NetData-ora10g-118 app]$
关闭监听及EM,EM我这里没装
确保磁盘有足够的空间,我删除了DB安装程序
[oracle@NetData-ora10g-118 ~]$ ls
10201_database_linux_x86_64.cpio database Desktop Documents Downloads Music Pictures Public Templates V
[oracle@NetData-ora10g-118 ~]$ rm -rf 10201_database_linux_x86_64.cpio
[oracle@NetData-ora10g-118 ~]$ rm -rf database/
[oracle@NetData-ora10g-118 ~]$ df -h
df: `/root/.gvfs‘: Permission denied
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_netdataora10g118-lv_root 18G 12G 5.2G 69% /
tmpfs 532M 456K 531M 1% /dev/shm
/dev/sda1 485M 35M 426M 8% /boot
/dev/sr0 4.2G 4.2G 0 100% /media/CentOS_6.5_Final
/dev/sr0 4.2G 4.2G 0 100% /mnt
解压
extracting: Disk1/install/images/rdbms_8.gif
inflating: Disk1/install/.oui
inflating: Disk1/install/unzip
inflating: Disk1/install/oraparamsilent.ini
inflating: Disk1/patch_note.htm
inflating: Disk1/runInstaller
creating: Disk1/response/
inflating: Disk1/response/patchset.rsp
inflating: README.htm
[oracle@NetData-ora10g-118 ~]$ unzip p8202632_10205_Linux-x86-64.zip
2.升级数据库软件
[oracle@NetData-ora10g-118 ~]$ su - root
wPassword:
[root@NetData-ora10g-118 ~]# xhost +
access control disabled, clients can connect from any host
[root@NetData-ora10g-118 ~]# su - oracle
[oracle@NetData-ora10g-118 ~]$ ls
Desktop Downloads Pictures Templates
Disk1 Music Public Videos
Documents p8202632_10205_Linux-x86-64.zip README.htm
[oracle@NetData-ora10g-118 ~]$
[oracle@NetData-ora10g-118 ~]$ cd Disk1/
[oracle@NetData-ora10g-118 Disk1]$ ls
install patch_note.htm response runInstaller stage
[oracle@NetData-ora10g-118 Disk1]$
如果出现以下错误
将/etc/redhat-release改成以下内容
[root@NetData-ora10g-118 Packages]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
[root@NetData-ora10g-118 Packages]#
如果出现以下将数据库shutdown immediate; kill-9 进程号 tnslsnr
升级数据库
以root 帐户运行以下脚本
/u01/app/oracle/product/10.2.0/db_1/root.sh
[root@NetData-ora10g-118 Desktop]# /u01/app/oracle/product/10.2.0/db_1/root.sh
Running Oracle 10g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/10.2.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
directory...
...opying dbhome to
...opying oraenv to
...opying coraenv to
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@NetData-ora10g-118 Desktop]#
3.数据库升级更新数据库字典
opyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 683671552 bytes
Fixed Size 2098752 bytes
Variable Size 331352512 bytes
Database Buffers 343932928 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
查询system表空间剩余空间
SQL> select tablespace_name, sum(bytes)/(1024*1024) "Free M" from dba_free_space where tablespace_name = ‘SYSTEM‘ group by tablespace_name;
TABLESPACE_NAME Free M
------------------------------ ----------
SYSTEM 104.3125
SQL> SPOOL patch.log
SQL>@?/rdbms/admin/catupgrd.sql #这里要花段时间.长我花了11分钟
重新编译失效的对象
SQL> spool off
SQL> shutdown immedite
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 683671552 bytes
Fixed Size 2098752 bytes
Variable Size 339741120 bytes
Database Buffers 335544320 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL>
SQL>@?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/catalog.sql;
SQL> @?/rdbms/admin/catproc.sql;
Package body created.
0 rows created.
PL/SQL procedure successfully completed.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 683671552 bytes
Fixed Size 2098752 bytes
Variable Size 385878464 bytes
Database Buffers 289406976 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
查询是否有无效的对象
SQL> select owner,object_name,subobject_name,object_type,status from dba_objects where status<>‘VALID‘;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
SYS
AQ$_AQ_SRVNTFN_TABLE_F
VIEW INVALID
SYS
AQ$AQ_SRVNTFN_TABLE
VIEW INVALID
SYS
AQ$_SCHEDULER$_JOBQTAB_F
VIEW INVALID
SYS
AQ$SCHEDULER$_JOBQTAB
VIEW INVALID
SYS
AQ$SCHEDULER$_JOBQTAB_R
VIEW INVALID
SYS
AQ$_SCHEDULER$_EVENT_QTAB_F
VIEW INVALID
SYS
AQ$SCHEDULER$_EVENT_QTAB_R
VIEW INVALID
SYS
AQ$_AQ$_MEM_MC_F
VIEW INVALID
SYS
AQ$_ALERT_QT_F
VIEW INVALID
SYS
AQ$ALERT_QT_R
VIEW INVALID
10 rows selected.
SQL> select status from v$instance;
STATUS
------------
OPEN
1 row selected.
查询版本号号
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
5 rows selected.
SQL> col comp_name format a30
SQL> col version format a30
SQL> col status format a10
SQL> SELECT comp_name, version, status FROM dba_registry;
COMP_NAME VERSION STATUS
------------------------------ ------------------------------ ----------
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Packages and T 10.2.0.5.0 VALID
ypes
2 rows selected.
SQL> select username from dba_users;
USERNAME
------------------------------
OUTLN
SYS
SYSTEM
TEST
DBSNMP
TSMSYS
DIP
ORACLE_OCM
8 rows selected.
SQL>
开启监听
[oracle@NetData-ora10g-118 Disk1]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 26-JUL-2014 18:55:26
Copyright (c) 1991, 2010, 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=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NetData-ora10g-118)(PORT=51518)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 26-JUL-2014 18:55:27
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=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NetData-ora10g-118)(PORT=51518)))
Services Summary...
Service "NETDATA" has 1 instance(s).
Instance "NETDATA", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@NetData-ora10g-118 Disk1]$
升级完毕!!!
升级参照的 Kevin.Lu 非常感谢他提供的文档